MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

Hysen.


Posted by Johnny on December 01, 2001 5:13 AM

Try this site, have a lot about your subject

http://www.cpearson.com/excel/ExcelPages.htm

Johnny

Posted by Aladin Akyurek on December 01, 2001 1:00 PM

Assuming that you have start hour in A and end hour in B from 2nd row on,

in C2 enter: =MOD(B2-A2,1) [ gives total ]

in D2 enter: =MOD(IF(B2<="24:00"+0,B2,"06:00"+0)-IF(A2<"22:00"+0,("22:00"+0),A2),1) [ gives hours worked between 22:00 and 06:00 ]

Select B2:C2 and copy down.

Aladin

=====

Posted by norman jones on December 02, 2001 1:34 PM

Posted by norman jones on December 02, 2001 1:35 PM

Posted by normanjones on December 02, 2001 1:36 PM

Posted by normanjones on December 02, 2001 1:40 PM

try using the following:-=IF(C4>D4,D4+1-C4,D4-C4)
i was using C4 as start time and D4 as end time
hope it works

Posted by norman jones on December 02, 2001 1:42 PM

forgot, use format for cells [h]:mm

Posted by Hysen on December 03, 2001 4:36 AM

Thanks Guys. I used Aladin's formula to calculate hrs worked between 2200 & 600 , but I encounter problems when the end time is beyond 6am. For eg
if I start at 2am and finish at 7am, the answer should be 4hrs. Formula gives me 10 hrs? Actually even with start at 2am and finish at 5am i get 7hrs?. Is there a way around this dilemma.
Once again thanks.

Posted by Aladin Akyurek on December 03, 2001 9:21 AM

Hysen --

Gee, I've been pretty sloppy. Try this one & please test it thoroughly:

=MOD(IF(B2<="24:00"+0,B2,"06:00"+0)-IF(A2<="06:00"+0,A2,IF(A2<"22:00"+0,"22:00"+0,A2)),1)

Hope I got it right this time.

Aladin