Time Logic

[tarus987]

Hello,


I was hoping someone could assist in correcting this expression. I am attempting to calculate the total number of hours minus the lunch break, and convert it to hour format.



Total Hours: DateDiff("n",[ShiftStart],[EndShift])+([Total Lunch])/60

 

[plog]

1. So how's that not working?

2. Demonstrate what you want to occur with sample data.

3. I see addition and division in there, not subtraction.

 

[tarus987]

I made a mistake in part of the query. Therefore the lunch column shows negative values such as -30, -60, -45. But even with this being the case, if I remove the /60 portion, it reflects 480 as being the total amount of minutes. But once I add the /60 back to the expression, it reflects 509.5

 

[tarus987]

 

[tarus987]

I was able to correct this issue by adding additional parenthisis. However, I was looking to get the answer in hour format instead of decimal format.

Such as 7hours 46min would reflects as 7.46 hours instead of 7.77 in decimal format.



Hours: (DateDiff("n",[ShiftStart],[EndShift])+([Total Lunch]))/60

 

[Gasman]

You appear to be only dividing the LunchHours by 60 and not the whole amount?

 

[tarus987]

Yes, I corrected the expression by adding parenthesis to the equation.

Hours: (DateDiff("n",[ShiftStart],[EndShift])+([Total Lunch]))/60

 

[tarus987]

But now I'm trying to change the format from decimal to hour format.

 

[Gasman]

A quick Google

https://access-programmers.co.uk/forums/showthread.php?t=189480

 

[plog]

But now I'm trying to change the format from decimal to hour format.

Please define "Hour" format. Example would help.

 

[tarus987]

I was able to accomplish this with the following code...


HoursWorked: DateDiff("n",[Total Lunch],[Hours])\60 Mod 60 & ":" & Format(DateDiff("n",[Total Lunch],[Hours]) Mod 60,"00")

 

[The_Doc_Man]

You could have simplified this as follows:

TimeWorked = ( ( [OutToLunch] - [ShiftStart] ) + ( [EndShift] - [ReturnLunch] ) )

Note that the units of "TimeWorked" would be DAYS and fractions thereof. Then, as long as those numbers don't cross a date boundary,

HoursWorked = Format( TimeWorked, "hh:nn" )

BUT that method would suffer from two weaknesses - first, that if the worker didn't take lunch, you would need a different formula, and second, if any shift crossed midnight, you would have issues in correcting the hours for that shift, which would appear as negative numbers in that case.