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
