Formula to calculate time into 2nd day

[Shazz]

Hi All,

I want to calculate the number of hours worked on a night shift, so the hours go from say a Friday Night in Saturday Morning, at the moment I am using =(Q18-P18) which works fine within the same day.

Can anyone advise how to adapt the formula to calculate into the following day. Hours needed are as below.

19:00 - 07:00

Thanks ever so much.

Shazz
x

 

[steve the fish]

Its probably safe to assume nobody works for over 24 hours in one shift so you could do:

=Q18-P18+(P18>Q18)

 

[Shazz]

This is showing the result as 0.5 instead of 12:00??

Shazz
x

 

[steve the fish]

0.5 is 12:00. Just format to time.

 

[Shazz]

I have another problem, when adding the Hours Worked for each it is not calculating properly,

12:00 + 10:00 + 10:00 + 12:00 is adding up as 22:00 instead of 44:00.

Can anyone help with this too.

Shazz
x

I am putting this in it's own post.

 

[Shazz]

0.5 is 12:00. Just format to time.

Silly me, thanks so much. That works fine now.

Shazz
x

 

[steve the fish]

For your other one format to custom [h]:mm