Counting time within time periods!

**thegooser123** · 09-06-2011, 04:25 PM

Your all gonna love this one!

I have a colum A as shift start date / time and colum B of Shift End date / time.

We have two rates of hourly pay depending on what hours in the day the staff worked. Rate 1 is between 07:00 and 20:00, Rate 2 is between 20:00 and 07:00.

I'd like to have colum C as hours the staff worked in rate 1, and colum D as number of hours that they worked in rate 2.

e.g
Colum A: 06/09/11 03:00 - Start date/time
Colum B: 06/09/11 23:30 - End date time
Colum C: 7.5 or 07:30 - hours worked on rate 1 (3am until 7am is 4 hours plus 8pm untill 11:30pm is 3 and a half hours)
Colum D: 11 or 11:00 (7am until 8pm is 11 hours)

This sounds really simple, but it has been baking my noodle for a loooooong time. Any ideas?

**TMS** · 09-06-2011, 05:06 PM

How is 7 AM to 8 PM 11 hours? 8 to 8 is usually 12 hours so 7 to 8 would be 13.

Regards

**thegooser123** · 09-06-2011, 05:13 PM

Originally Posted by TMShucks

How is 7 AM to 8 PM 11 hours? 8 to 8 is usually 12 hours so 7 to 8 would be 13.

Regards

Typo

Regards

**TMS** · 09-06-2011, 05:13 PM

Assuming data in row 2:

C2: =(B2-A2)-((MIN((B2-INT(B2)),TIME(20,0,0)))-(MAX((A2-INT(A2)),TIME(7,0,0))))

D2: =(MIN((B2-INT(B2)),TIME(20,0,0)))-(MAX((A2-INT(A2)),TIME(7,0,0)))

Note: some extra brackets to separate the stages ;-)

Regards

**thegooser123** · 09-06-2011, 05:43 PM

Originally Posted by TMShucks

Assuming data in row 2:

C2: =(B2-A2)-((MIN((B2-INT(B2)),TIME(20,0,0)))-(MAX((A2-INT(A2)),TIME(7,0,0))))

D2: =(MIN((B2-INT(B2)),TIME(20,0,0)))-(MAX((A2-INT(A2)),TIME(7,0,0)))

Note: some extra brackets to separate the stages ;-)

Regards

Ideal... works great... almost.

06/09/11 04:00 - 06/09/11 04:30 - returns a value of 03:00 instead of 00:30

Any ideas?

**TMS** · 09-06-2011, 07:09 PM

Maybe:

C2: =IF((A2-INT(A2))<TIME(7,0,0), MIN((B2-INT(B2)),TIME(7,0,0))-(A2-INT(A2)),0)+IF((B2-INT(B2))>TIME(20,0,0), (B2-INT(B2))-MAX((A2-INT(A2)),TIME(20,0,0)),0)

D2: =(B2-A2)-(IF((A2-INT(A2))<TIME(7,0,0), MIN((B2-INT(B2)),TIME(7,0,0))-(A2-INT(A2)),0)+IF((B2-INT(B2))>TIME(20,0,0), (B2-INT(B2))-MAX((A2-INT(A2)),TIME(20,0,0)),0))

Regards

**thegooser123** · 09-06-2011, 07:31 PM

Originally Posted by TMShucks

Maybe:

C2: =IF((A2-INT(A2))<TIME(7,0,0), MIN((B2-INT(B2)),TIME(7,0,0))-(A2-INT(A2)),0)+IF((B2-INT(B2))>TIME(20,0,0), (B2-INT(B2))-MAX((A2-INT(A2)),TIME(20,0,0)),0)

D2: =(B2-A2)-(IF((A2-INT(A2))<TIME(7,0,0), MIN((B2-INT(B2)),TIME(7,0,0))-(A2-INT(A2)),0)+IF((B2-INT(B2))>TIME(20,0,0), (B2-INT(B2))-MAX((A2-INT(A2)),TIME(20,0,0)),0))

Regards

Very close mate, and I do appreciate all your help.

however....

26/08/2011 05:45 27/08/2011 02:00 produces a negative
and
05/09/2011 17:00 06/09/2011 01:30 produces a zero.

do you know why this might be?

**TMS** · 09-06-2011, 08:15 PM

do you know why this might be?

Because you keep moving the goalposts, maybe? ;-)

Your previous examples were for the same day.

I'll need to sleep on it.

**daddylonglegs** · 09-06-2011, 08:33 PM

What's the maximum shift length? Assuming that the end date will always be either the same day or the next (compared to start date) then try this formula in C2

=(INT(B2)>INT(A2))*13+MEDIAN(MOD(B2,1)*24,7,20)-MEDIAN(MOD(A2,1)*24,7,20)

and in D2

=(B2-A2)*24-C2

format C2 and D2 as number with 2 decimal places

[note the second formula uses the result of the first]

These will give the results as a decimal number of hours, e.g. for your last two examples you'll get 13 and 7.25 for the first and 3 and 5 for the second - is that correct?