Calculating elapsed time within a time period (1 Viewer)

[cdoner]

Greeting everyone. This is my first post.

I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. But now here's where I am asking for help.

Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date. Forgive me for mixing what may be VBA language with function terminology used within the expression builder, I merely mean to express my logic on the process.

IF [PunchOut] ISNOT Saturday,Sunday

EXIT FUNCTION

ELSE

IF [PunchOut] ISNOT Between Midnight and 0559 hours

EXIT FUNCTION

ELSE

DATEDIFF ("n", <MIDNIGHT>, [PunchOut])

My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.

 

[pbaldy]

In the DateDiff() you can use the DateValue() function to get midnight. For the others, the Weekday() and TimeValue() functions may prove useful.

 

[cdoner]

Thanks Paul (pbaldy) for pointing me in the right direction. After a little trial and error I arrived at the following expression:

OtherPay: IIf(Weekday([PunchOut])=1 Or Weekday([PunchOut])=7,IIf(TimeValue([PunchOut])>=#12:00:00 AM# And TimeValue([PunchOut])<=#6:00:00 AM#,DateDiff("n",DateValue([PunchOut]),[PunchOut])," "))

However, I now realize that I need to account for when [PunchIn] is between Midnight and 6am. Otherwise this expression will calculate the DateDiff from Midnight regardless. Back to the drawing board.

 

[pbaldy]

No problem, post back if you get stuck. When I start to see nested functions getting complicated like that, I would start to lean towards a custom VBA function that accepted the 2 times as inputs and returned the desired result. For me, it's easier to follow the logic in code, plus you'd only have to maintain it in one place, handy when changes need to be made.

 

[Geotch]

It always helps me to do several if statements in queries and build off the previous if statement. Do one at a timers get the end result.