Calculate hours worked outside regular shift using date/time and iff condition

[access_learner via AccessMonster.com]

Employee Shift_Starts Shift_Ends Regular_Shift_Starts
Regular_Shift_Ends
John 06/01/2009 06:00:00 06/01/2009 14:30:00 06/01/2009 09:00:00 06/01/2009
18:00
Mary 06/01/2009 13:30:00 06/01/2009 22:00:00 06/01/2009 09:00:00 06/01/2009
18:00
Dave 06/01/2009 09:00:00 06/01/2009 18:00 06/01/2009 09:00:00 06/01/2009 18:
00
Jane 06/01/2009 17:00:00 06/01/2009 21:00:00 06/01/2009 09:00:00 06/01/2009
18:00
Jack 06/01/2009 22:30:00 06/02/2009 07:00:00 06/01/2009 09:00:00 06/01/2009
18:00
Larry 06/01/2009 00:30 06/02/2009 09:30 06/01/2009 09:00:00 06/01/2009 18:00

Regular Shift Hours from 9:00 am to 6:00 pm
John works from 6:00 am to 2:30 pm
Mary works from 1:30 pm to 10:00 pm
Dave works from 9:00 am to 6:00 pm
Jane works from 5:00 pm to 9:00 pm
Jack works from 9:30 pm to 7:00 am
Larry works from 12:30 am to 9:30 am

I would like to know how many total hours each employee works outside of
Regular Shift (<9am and >6pm) using date/time and iff conditions to satisfy
any scheduled time using military clock.

All employees start and end shift at different time using 24 military time.
For example, if John starts at 6 am and ends at 2:30 pm then he worked 3
hours before Regular_Shift_Starts. Mary worked 4 hours outside of Regular
Shift Hours. Dave worked 0 hours outside of Regular Shift Hours. Jack
worked 9.5 hours (entire shift or 100 % outside of Regular Shift Hours).
Larry worked 8.5 hours outside of Regular Shift Hours from total of 9 hours
since he worked until 9:30 am.


Thank you.

 

[Allen Browne]

You didn't like the answer you were already given for this?

 

[access_learner via AccessMonster.com]

You didn't like the answer you were already given for this?

Total hours Employee worked in shift were correct. As you can see each
Employee starts and ends shift at different time in 24 hour period. It did
not give accurate answer for all the different start and end times. For
example, if Employee started the shift after the Regular Scheduled Hours it
did not give the correct answer. I think I need to use some iff statements
so that all the times are covered to give correct answer. However, I am not
familiar with it. If employee starts works after 6 pm and finishes before 9
am then he did not work any hours during Reg_Sch_Hrs. Another case is if
Employee starts 11 pm to 10 am should give only 1 hour worked during
Reg_Sch_Hrs.

I need your help.

Thank you.

 

[Allen Browne]

In the example you gave, each of the Date/Time fields contained a date as
well as a time. That should make the calculation accurate, even if the shift
does roll over past midnight.