Adding hrs in a timesheet only if hrs worked span a time range
Posted by Hysen on December 01, 2001 4:15 AM
I have set up a Timesheet, which calculates the no. of total hrs worked between start time and end time. The work hours can go over midnight. E.g. start at 22:00 and end at 5:00 the next morning.
My question is: In addition to working out the total hrs worked, which I have done, I also need to calculate the number of hours & minutes (out of total hrs worked) that a employee works between 22:00 and 6:00. There are many scenarios, too many for using an if statement. Egs are;
start@ end@ Hrs between 22:00 & 6:00 worked
18:00 2:00 4 hrs
23:00 7:00 7 hrs
14:00 22:30 30 mins.
Is there a formula I could use or do I have to resort to a macro.
Thanking in advance.