Computing Overtime: A Longstanding Question

[Aladin Akyurek]

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 ]

[keithkemble]

The secret with time is to get the formatting right.
I am not sure I understand you plight but in order to get the correct evaluationof elapsed time you need :
5 cols
a start time
b

[keithkemble]

The secret with time is to get the formatting right.
I am not sure I understand you plight but in order to get the correct evaluation of elapsed time you need :
5 columns
a start time Format (custom hh:mm)
b finish time Format (custom hh:mm)
c formula to establish diff in decimal form = (B1-A1)+If((A1>B1,1) format general
d conversion to 24hr formula = (B1-A1)+If(A1>B1,1) Format time 13:30
e diff in hours ( * 60 in minutes reqd)formula =If(c1=0,24,D1*24) format general

If you then hide cols c and d you can then summate all column e ( e being the shift total in hours or minutes).
This I think would simplify your analysys.
kk

[keithkemble]

Further to my previous answer you can change column f to this and this will return zero if there is no start and finish times.

If you change the cell F1 to
=IF(AND(A1=0,B1=0,TRUE),0,IF(C!=0,24,D1*24))
This will test for nil entry in both start and finish times

This is because in our business we work 365 days and you could have the same start and finish time which would mean a day has expired
Hope this helps
kk

[IML]

Bravo. I started playing with a "simplified" boolean formula, but quickly dismissed it when head just about exploded thinking of the different options. At the end of day, things would be greatly simplified if a true time and date were placed in this rather than "tricking" excel into beleiveing it was the next day.

I have tried doing timecards with only cell formulas, the place had some flakey policies, such as allowing workers to clock in as soon as they walked in the door, even though the shift started an hour later, the forklift guy got a fifteen minute early start, the supervisor was paid from the moment he clocked in.............


You need to click Alt F11 and discover the Visual Basic Applications Editor.

When you are done, you will:

Have some (forms toolbox) buttons on the sheet.

Be well acquainted with "Cells(row,col).value" notations.

Become good with For loop = start to finish...Next control structures.

I tell you, I am embarrassed to admit how hard I beat the dead horse of cell formulas before I decided to tap Alt F11 and discover VBA

Good Luck!

[Aladin Akyurek]

On 2002-02-17 20:09, IML wrote:
Bravo. I started playing with a "simplified" boolean formula, but quickly dismissed it when head just about exploded thinking of the different options. At the end of day, things would be greatly simplified if a true time and date were placed in this rather than "tricking" excel into beleiveing it was the next day.

Ian: Maybe you're thinking of registering the date as well as the time when entering start and end times. This has come up at the Microsoft news group for worksheet functions, giving me the hope that I could drop further effort devising a single formula. But, alas. I don't know why that didn't work. Here is the thread where that has been proposed:

http://groups.google.com/groups?hl=e...4ax.com#link10

Another relevant thread is:

http://groups.google.com/groups?q=ca...=Google+Search

Both threads contain refs to the earlier versions of the formula I posted here.

Aladin