Time Logic (1 Viewer)

T

tarus987

New member

Local time

Yesterday, 22:24

Joined

Oct 2, 2018

Messages

9

• Oct 2, 2018

• #1

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

 

P

plog

Banishment Pending

Local time

Yesterday, 22:24

Joined

May 11, 2011

Messages

11,646

• Oct 2, 2018

• #2

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.

 

T

tarus987

New member

Local time

Yesterday, 22:24

Joined

Oct 2, 2018

Messages

9

• Oct 2, 2018

• #3

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

 

T

tarus987

New member

Local time

Yesterday, 22:24

Joined

Oct 2, 2018

Messages

9

• Oct 2, 2018

• #4

 

T

tarus987

New member

Local time

Yesterday, 22:24

Joined

Oct 2, 2018

Messages

9

• Oct 2, 2018

• #5

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

Enthusiastic Amateur

Local time

Today, 04:24

Joined

Sep 21, 2011

Messages

14,287

• Oct 2, 2018

• #6

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

 

T

tarus987

New member

Local time

Yesterday, 22:24

Joined

Oct 2, 2018

Messages

9

• Oct 2, 2018

• #7

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

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

 

T

tarus987

New member

Local time

Yesterday, 22:24

Joined

Oct 2, 2018

Messages

9

• Oct 2, 2018

• #8

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

 

Gasman

Enthusiastic Amateur

Local time

Today, 04:24

Joined

Sep 21, 2011

Messages

14,287

• Oct 2, 2018

• #9

A quick Google

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

 

P

plog

Banishment Pending

Local time

Yesterday, 22:24

Joined

May 11, 2011

Messages

11,646

• Oct 3, 2018

• #10

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

Click to expand...

Please define "Hour" format. Example would help.

 

T

tarus987

New member

Local time

Yesterday, 22:24

Joined

Oct 2, 2018

Messages

9

• Oct 17, 2018

• #11

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

Immoderate Moderator

Staff member

Local time

Yesterday, 22:24

Joined

Feb 28, 2001

Messages

27,179

• Oct 17, 2018

• #12

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.