Hi,

In a time sheet I have created, there is

start/end work time which works O.K. My

problem is, that between 0:00 and 6:00 (night

shift) there is different pay rate, so I have to

calculate those night hours (using IF

expression, I believe....) and represent day and

night working hours separately. Question: how

to make formula checking the "End Work"

before midnight and split working hours in 2

different cells?

Thank you

Hi, Here are some formulas that should get you going:

B C D E 2 Start End Today After midnight hours hours 3 22:00 6:15 2 6.25 4 6:00 14:00 8 0The times in columns B and C must be pure time, no date information.

You can either use data validation to ensure that the input data is always less than 1 (Excel holds time in decimal part of the number and dates in the integer or whole number part)

An alternative is to add complexity to the formulas to only take the decimal part of the numbers.The formula in Cell D3 is =24*IF(C3>B3,C3-B3,1-B3)

This assumes that if the finish time is 'earlier' than the start time, then the finish is after midnight.

If the finish time is after midnight, The Today hours calculates the duration between start time and midnight (Excel value =1.00000), i.e. the start of the next day.The formula in Cell E3 is =IF(C4<B4,24*C4,0)

This formula returns 0 if the finish time is greater than the start time.

If the finish time is smaller than the start time it takes the finish time as the duration.In both formulas the hours are calculated as 24 * the Excel duration value.

For example:

In Excel 0.5 is 12 noon, i.e., half way through the day

24 * 0.5 =12Parts of hours are not converted to minutes in this example, but for pay calculations you probably don't want minutes, just fractions of hours.

Regards

thank you.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History