Problem in subtracting hrs work from regular hours. Need Help

I am Tim, The following are the computation I did earlier. The System I'm producing is all hours and no rates. Part of Daily Time Record.

(query table)

Field Data Type Format Computation/Condition

TimeIN Date/Time hh:mm "Short Time" none
TimeOUT Date/Time hh:mm "Short Time" none
HrsWrk Date/Time hh:mm "Short Time" 24-[TimeIN]+[TimeOUT]
RegHrs Date/Time hh:mm "Short Time" IIf([HrsWrk]>Format("8:00","Short Time"),Format("8:00","Short Time"),[HrsWrk])
OT Date/Time hh:mm "Short Time" IIF([HrsWrk]>[RegHrs],[HrsWrk]-Format("8:00","Short Time),"No OT")

The problem here is the OT. #Error is the Output, Why is that? Can you correct my Computation/Condition of OT? need it ASAP

Thanks

Tim

Hi Tim

Try this: HrsWrk = Format([TimeIN] -1 -[TimeOUT], "Short Time")
OT = IIf([HrsWrk]>[RegHrs],[HrsWrk]-[RegHrs],"No OT")
Have not tested, just a thought
lwells

Hi There,

I already tried the formula. HrsWrk is fine but OT display #error result when using [HrsWrk]-[RegHrs]. Is There another way to solve OT?

Hi Tim

Use the following TimeValue in your syntax:

IIf([HrsWrk]>[RegHrs],Format(TimeValue([HrsWrk])-TimeValue([RegHrs]),"Short Time")," No OT")

This should work. I am using Access 2002, so I don't know if Access 2000 has the TimeValue expression or not. If not you may have an equalant. Just convert the time back into a value so it can be subtracted.
lwells

Hello 1Wells,

Thanks for helping me out. Finally, my problem is solved. Now, I only have one more thing to ask you. I already have the condition in getting the night differential. In this kind of system, night differential or ND is from 06:00am to 05:00pm (17:00). I used IIF function but some part of the result doesn't have ND even the Time In and Time Out range is within ND Range.

Here is what I did: Can you correct it for me. Thanks

ND: IIf([TimeIN]=Format("01:00","Short Time") Or [TimeIN]<=Format("6:00","Short Time") Or [TimeIN]>Format("20:00","Short Time") Or [TimeIN]<=Format("0:00","Short Time") Or [TimeOUT]=Format("01:00","Short Time") Or [TimeOUT]<=Format("06:00","Short Time") Or [TimeOUT]>Format("20:00","Short Time") Or [TimeOUT]<=Format("0:00","Short Time"),Format(#2:00:00 AM#+[TimeOUT],"Short Time"),"NONE")

Note: Time IN and Time OUT is our bases in getting the ND. Provided that your given Time IN and Time OUT is within 6am-5pm, otherwise there is no ND (Night Differential)

Tim

Hi Tim,

According to your arguments

Time In = 1:00 AM or
Time In <= 6:00 AM or
Time In > 8:00 PM or
Time In <= 12:00 AM
Probably should look more like TimeValue[TimeIn] >= TimeValue[20:00] AND <= TimeValue[06:00] to capture the time between those time ranges.

Time Out = 1:00 AM or
Time Out <= 6:00 AM or
Time Out > 8:00 PM or
Time Out <= 12:00 AM
Probably should look more like TimeValue[Time Out] >= TimeValue[20:00] AND <= TimeValue[06:00] to capture the time between those time ranges.

The use of OR in your statement will pass true for example if the TimeIn was 7:00 PM because it is less than 12:00 AM. It did not pass true for the first three arguments but did pass the fourth. The use of AND will force the time to be within the arguments in order for it to be true otherwise will return false.

I wasn't sure what the true condition of your IIf statement was suppose to do, but in order to add or subtract time you will need to use the TimeValue expression TimeValue[02:00] + TimeValue[TimeOUT]

See if this helps in clearing things up a bit.
lwells

Thanks IWells... That could be a big help. No wonder my system doesn't give the exact result. Anyway, Do you have any idea how to get Sunday for my another condition.

The idea here is If the Login Date is Sunday then remark is 1. All I need is to count the number of Sunday of each employee within a given [starting date] and [ending date]

Thanks.

Tim

We can exchange ideas if ever you get stuck in your program problem too.

Hi Tim,

I will make a couple of assumptions here.
1) You have already established how to identify the login date as being Sunday
2) you have entered a value of 1 into a field on your table to indicate that it was a Sunday that the user logged in.

So if these assumptions are correct, then the use of the DCount will work.

DCount ("[TableName].[FieldName]","[TableName]","[TableName].[FieldName]=1")

or without first determining whether a [LoginDate] is a Sunday or not, you can still use the DCount method with this criteria

DCount("[LoginDate]", "TableName", "IIf ( Format ([LoginDate] , ""dddd"")=""Sunday"" , 1)")

That will count the number of Sundays present in the LoginDate Field of your table without having to first determine which dates are Sundays. This would eliminate the need of a separate field to place the value 1 into. To count the number of Sundays within a range of dates, all you would have to do is enter the parameters for the Starting and Ending date time period in the query criteria grid and then change the syntax from TableName to the QueryName in the DCount. You can also add the additional criteria to the DCount itself. Just a matter of preference and how complex the criteria becomes to be able to enter the syntax properly. Performance will be pretty much the same either way, however a query design might make it easier to change the date ranges than writing additional code to handle the variables.

I believe this is what you were looking for.
lwells

Thanks Iwells,

you are right! This is what I'm looking for. I try to apply your solution to my application.

Tim

Hello guys,

Who could help me with this problem? Need help ASAP. I already have this DTR application but my problem is getting the ND. Here are the sample output of ND (Night Differential): 19:00 (today) to 05:00 (next day) ND, 22:00 (today) to 7:00 (next day) ND, 03:00 (today) to 11:00 (today) NO ND, 22:00 (today) to 06:00 (next day) ND, 05:00 (today) to 19:00 (today) NO ND, 06:00 (today) to 22:00 (today) NO ND. How to produce this output where TimeIN and TimeOUT is within the range of 22:00 (today) to 06:00 (next day), the output is ND=2+TimeOUT otherwise, the output is ND=0 ? Thanks.... Kindly help me as soon as possible. TIM

Hello Iwells,

I already tried your suggestion but the problem now is when trying to login at 19:00 (today) to 7:00 (the next day). The output should be ND=2+LogOUT Time. In the query, the output doesn't have ND. What should I do now?

Hi Tim,

Yes you are correct. Using the TimeValue will only work within the same 24 hour period otherwise access starts over with 00:00 when it comes to Midnight. You have to make your additions and subtractions a little different when going over this "Midnight" issue.

This is the formula that I suggested earlier for handling going across "Midnight"
Format([StartTime] -1 -[EndTime], "Short Time")

So the logic here is that if the Start Time was 19:00 and the End Time was 05:00 the next day, the total time would be 10 hours. But when using the TimeValue Method "Format(TimeValue([Start Time])-TimeValue([End Time]),"Short Time")" the resulting time is 14:00. (19:00 - 05:00 = 14:00 simple mathmatics) So you can use this difference in these two methods of calculating the time values to create a True/False IIf statement.

ND=IIf(Format([StartTime] -1 -[EndTime], "Short Time")<>Format(TimeValue([Start Time])-TimeValue([End Time]),"Short Time"),2+[End Time],"No ND")

First we compare the two formulas and if they don't match, then we have crossed Midnight, and so you add the 2 to your End Time (if that was correct) and if it was False, because they were equal, then that would mean that the time occurred during the same day.

See if you can apply this logic to your own formula to establish whether or not the time had crossed midnight to be eligible for the Night Differential.

lwells

Hello Iwells,

Thanks. I already tried your suggestion but still 19:00 (Time In) and 07:00 (Time Out) display NO ND instead of 2+Time out. The object of this formula is to generate ND if Time In to Time Out pass 22:00,23:00,00:00 (24:00), up to 06:00. In my example, 19:00 to 07:00 did pass 22:00 because the series is from 19:00,20:00,21:00,22:00,23:00,00:00,01:00,02:00,03 :00,04:00,05:00,06:00 until 07:00. Therefore, If we logged on from 19:00 to 07:00 the output should be 2+Time out. Below is the result when I applied your suggestion. Guard QueryDatedpositionTimeInTimeOutHrsWrkRegHrsOTND11/6/2004San Antonio Village - Pasig City22:006:0008:0008:00NONE06:0011/7/2004St. Paul College - Quezon City17:003:0010:0008:0002:0003:0011/8/2004Sacred Heart School7:0021:0014:0008:0006:00No ND11/9/2004St. Paul College - Pasig City21:005:0008:0008:00NONE05:0011/10/2004St. Paul College - Quezon City7:0019:0012:0008:0004:00No ND11/11/2004San Antonio Village - Pasig City19:007:0012:0008:0004:00No ND11/12/2004St. Paul College - Quezon City0:005:0005:0005:00NONENo ND11/13/2004Sacred Heart School18:007:0013:0008:0005:0007:0011/27/2004St. Paul College - Quezon City15:007:0016:0008:0008:0007:00
Note: When there is ND the formula is 2 hours + TimeOut otherwise ND is zero. Can you help me again? Thanks.

Hi Tim,

Sure, I will be more than happy to help. Go ahead and post the sql from your query so I can build a mock up and see what is taking place. The sample code I gave above worked with a simple database that I used, so something isn't working right. Then I can post the sql back for you to past into your query. If your query is also based on other queries let me have them as well, so I can get all the fields entered correctly.

lwells