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

Date

Work

Leave

Standby

Start

Finish

Break

Hours

x1

x1.5

x2

Call Out

Description of tasks

Thu 01 Feb 2018

13:00

03:00

00:30

13:30

06:30

03:00

04:00

<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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]

Date

Work

Leave

Standby

Start

Finish

Break

Hours

x1

x1.5

x2

Call Out

Description of tasks

Thu 01 Feb 2018

01:00

23:00

00:30

21:30

13:30

03:00

00:00

Fri 02 Feb 2018

07:00

19:00

00:30

11:30

11:30

00:00

00:00

Sat 03 Feb 2018

12:00

00:00

00:30

11:30

07:30

03:00

00:00

Sun 04 Feb 2018

22:00

03:00

00:30

04:30

####

01:00

04:00

Mon 05 Feb 2018

19:00

01:00

00:30

05:30

00:30

03:00

02:00

Tue 06 Feb 2018

07:00

17:00

00:30

09:30

09:30

00:00

00:00

Wed 07 Feb 2018

07:00

17:00

00:30

09:30

09:30

00:00

00:00

Thu 08 Feb 2018

07:00

17:00

00:30

09:30

09:30

00:00

00:00

<tbody>
</tbody>

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