Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Eons ago Hysen posted at the old board (see http://www.mrexcel.com/wwwboard/messages/7738.html ) the following question:
QUOTE
Posted by Hysen on December 01, 2001 at 04:15:56:
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.
UNQUOTE
My initial approach was top-down, which I had to abandon very quickly. I published at least 2 early (insufficient) versions of the ongoing struggle as a response to similar questions at a news group. The final formula that eventually appears to resolve the problem is the result of countless bottom-up efforts (whence many IFs!). It seems appropriate that I post it at this board where all this has started:
=IF(OR(AND(A2<= eCRIT,B2 >= sCRIT),AND(A2<= eCRIT,B2< eCRIT,B2-A2< 0),A2 = B2),
MOD(eCRIT-A2,1)+MOD(B2-sCRIT,1),
IF(AND(OR(A2*24< 12,NOT(B2-sCRIT > 0),B2*24 >= 12),NOT(AND(B2-A2< 0,A2*24 > 12,A2< sCRIT,B2 > eCRIT)),A2< sCRIT,B2 > eCRIT,A2*24<> 0,eCRIT< A2,B2< sCRIT,NOT(AND(A2*24< 12,B2*24< 12,A2 >= eCRIT,B2-A2< 0))),
0,
MOD(IF(B2<= eCRIT,B2,IF(B2 >= sCRIT,B2,eCRIT))-IF(A2<= eCRIT,A2,IF(A2<= sCRIT,sCRIT,A2)),1)))
where sCRIT and eCRIT defines the interval for overtime (e.g., 22:00 and 6:00 respectively, as in Hysen's case). Many thanks to Hysen Struga and Jon Crash who helped me to finalize it.
If anyone wants to devise an alternative or to shorten the one above, I can send a test set to experiment with.
Special Note. Ian: I think the peculiar problem that you noted wrt MOD in computing time differences seems to be not a problem here.
PS. It's a hard job to post lengthy formulas!
Additional Note (added on: Sun, Feb 17, 02). The formula was crippled because of the existence of a number of the less than signs (a troubling problem at the old board which appears to be still with us). If interested in the formula, just drop me a line at
akyurek@xs4all.nl
This message was edited by Aladin Akyurek on 2002-02-16 16:01
This message was edited by Aladin Akyurek on 2002-02-16 16:10
This message was edited by Aladin Akyurek on 2002-02-17 07:23
QUOTE
Posted by Hysen on December 01, 2001 at 04:15:56:
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.
UNQUOTE
My initial approach was top-down, which I had to abandon very quickly. I published at least 2 early (insufficient) versions of the ongoing struggle as a response to similar questions at a news group. The final formula that eventually appears to resolve the problem is the result of countless bottom-up efforts (whence many IFs!). It seems appropriate that I post it at this board where all this has started:
=IF(OR(AND(A2<= eCRIT,B2 >= sCRIT),AND(A2<= eCRIT,B2< eCRIT,B2-A2< 0),A2 = B2),
MOD(eCRIT-A2,1)+MOD(B2-sCRIT,1),
IF(AND(OR(A2*24< 12,NOT(B2-sCRIT > 0),B2*24 >= 12),NOT(AND(B2-A2< 0,A2*24 > 12,A2< sCRIT,B2 > eCRIT)),A2< sCRIT,B2 > eCRIT,A2*24<> 0,eCRIT< A2,B2< sCRIT,NOT(AND(A2*24< 12,B2*24< 12,A2 >= eCRIT,B2-A2< 0))),
0,
MOD(IF(B2<= eCRIT,B2,IF(B2 >= sCRIT,B2,eCRIT))-IF(A2<= eCRIT,A2,IF(A2<= sCRIT,sCRIT,A2)),1)))
where sCRIT and eCRIT defines the interval for overtime (e.g., 22:00 and 6:00 respectively, as in Hysen's case). Many thanks to Hysen Struga and Jon Crash who helped me to finalize it.
If anyone wants to devise an alternative or to shorten the one above, I can send a test set to experiment with.
Special Note. Ian: I think the peculiar problem that you noted wrt MOD in computing time differences seems to be not a problem here.
PS. It's a hard job to post lengthy formulas!
Additional Note (added on: Sun, Feb 17, 02). The formula was crippled because of the existence of a number of the less than signs (a troubling problem at the old board which appears to be still with us). If interested in the formula, just drop me a line at
akyurek@xs4all.nl
This message was edited by Aladin Akyurek on 2002-02-16 16:01
This message was edited by Aladin Akyurek on 2002-02-16 16:10
This message was edited by Aladin Akyurek on 2002-02-17 07:23