Seperating hours from a range of time.

[BigRobCoUk]

Hello All,

I have in the past found loads of help from other posts on this site, but I am a bit stumped now.

I am trying to create a timesheet that can calculate a shift whether that be a day shift or a night shift that spans midnight. Giving the total hours (this is the easy bit for me)

But....

Then creating seperate hours based on the time of day that they are in.

For instance

[TABLE="class: grid, width: 1238"]
<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date
[/TD]
[TD]Work
[/TD]
[TD]Leave
[/TD]
[TD]Standby
[/TD]
[TD]Start
[/TD]
[TD]Finish
[/TD]
[TD]Break[/TD]
[TD]Hours
[/TD]
[TD]x1
[/TD]
[TD]x1.5
[/TD]
[TD]x2
[/TD]
[TD]Call Out[/TD]
[TD]Description of tasks[/TD]
[/TR]
[TR]
[TD="align: right"]Thu 01 Feb 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]03:00[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]06:30[/TD]
[TD="align: right"]03:00
[/TD]
[TD="align: right"]04:00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Where column x1 are the hours from 0600-2000
column x1.5 are the hours from 2000-2300
column x2 are the hours from 2300-0600

The break column will always be deducted from the x1 rate column also.

Is anyone able to put me on the right track for this.

Your help would be much appreciated.

Thanks in advance.

Rob

 

[AlphaFrog]

x1
=MEDIAN(6/24,F2+(F2< E2),20/24)-MEDIAN(6/24,E2,20/24)-G2


x1.5
=MEDIAN(20/24,F2+(F2< E2),23/24)-MEDIAN(20/24,E2,23/24)


x2
=MEDIAN(23/24,F2+(F2< E2),30/24)-MEDIAN(23/24,F2,30/24)

 

[AlphaFrog]

Correction: x2 should be this...

=MEDIAN(23/24,F2+(F2< E2),30/24)-MEDIAN(23/24,E2,30/24)

 

[BigRobCoUk]

x1
=MEDIAN(6/24,F2+(F2< E2),20/24)-MEDIAN(6/24,E2,20/24)-G2


x1.5
=MEDIAN(20/24,F2+(F2< E2),23/24)-MEDIAN(20/24,E2,23/24)


x2
=MEDIAN(23/24,F2+(F2< E2),30/24)-MEDIAN(23/24,F2,30/24)

Thanks AlphaFrog for a quick response.

I have tried the formula,

Can you please help when I put in the start time from 00:00 to 24:00 it doesnt seem to calculate and of the x2 hours on that column.

Thanks again

 

[AlphaFrog]

Can you please help when I put in the start time from 00:00 to 24:00 it doesnt seem to calculate and of the x2 hours on that column.

Not sure what you are asking.

Don't use 24:00 as a start-time. That's not consistent with how you enter end-times that do not indicate they are on the next day.

 

[BigRobCoUk]

[TABLE="class: grid, width: 1230"]
<tbody>[TR]
[TD]
Date
[/TD]
[TD]Work[/TD]
[TD]Leave[/TD]
[TD]Standby[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Break[/TD]
[TD]Hours[/TD]
[TD]x1[/TD]
[TD]x1.5[/TD]
[TD]x2[/TD]
[TD]Call Out[/TD]
[TD]Description of tasks[/TD]
[/TR]
[TR]
[TD="align: right"]Thu 01 Feb 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]21:30[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]03:00[/TD]
[TD="align: right"]00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Fri 02 Feb 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Sat 03 Feb 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"]07:30[/TD]
[TD="align: right"]03:00[/TD]
[TD="align: right"]00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Sun 04 Feb 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]03:00[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]04:30[/TD]
[TD="align: center"]####[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]04:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Mon 05 Feb 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]05:30[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]03:00[/TD]
[TD="align: right"]02:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Tue 06 Feb 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]17:00
[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]09:30[/TD]
[TD="align: right"]09:30[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Wed 07 Feb 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]09:30[/TD]
[TD="align: right"]09:30[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Thu 08 Feb 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]09:30[/TD]
[TD="align: right"]09:30[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi AlphaFrog,

What I am trying to say is that is is not calculating the hour between 2300 and 2400 on the sheet in the x2 column.

Also when a time is calculated over the midnight time the times are not adding up. the combine x1, x1.5 and x2 should equal the total hours in column H.

I have filled in some example shifts into the sheet above.

Thanks

 

[AlphaFrog]

x1
=IF(E2 > 20/24,0,MEDIAN(6/24,F2+(F2< E2),20/24)-MEDIAN(6/24,E2,20/24)-G2)


x1.5 (if x1 is zero, the Break is subtracted here)
=MEDIAN(20/24,F2+(F2< E2),23/24)-MEDIAN(20/24,E2,23/24)-(G2*(I2=0))


x2
=IF(E2< 6/24,MEDIAN(0,F2,6/24)-MEDIAN(0,E2,6/24), MEDIAN(23/24,F2+(F2< E2),30/24)-MEDIAN(23/24,E2,30/24))

 

[BigRobCoUk]

AlphaFrog,

You are a star, thanks for doing this for me. It works a treat.

I have no idea how but it does. I think I'm going to have to get some excel training under my belt. The last 'intermediate' one I done at work taught us how to use ctrl+v and p instead of right clicking. lol.

Although I think I am a bit past that stage I now know there is a lot more to learn for me...

Thanks again.

Rob