work

[Ghana]

I work day and night shifts and need to create a formula to take the difference between start time and finish time minus a meal break. 30 minute meal break if I work over 5 hours and 60 minute break if I work over 10 hours.
I use A2 as start time, B2 as finish time.
If anyone can please help will be appreciated

 

[AOB]

=B2-A2-IF((B2-A2)*60*24>=(10*60),(1/24),IF((B2-A2)*60*24>=(5*60),(0.5/24),0))

 

[Ghana]

=B2-A2-IF((B2-A2)*60*24>=(10*60),(1/24),IF((B2-A2)*60*24>=(5*60),(0.5/24),0))

Thank you so much

 

[Ghana]

=B2-A2-IF((B2-A2)*60*24>=(10*60),(1/24),IF((B2-A2)*60*24>=(5*60),(0.5/24),0))

Hi I have used your code and it works great for day shifts. but when the shift is 23:00-07:00 it doesn't work it gives #####. Can you help please.

 

[AOB]

Works for me, are the values in A2 (start time) and B2 (finish time) dates & times (e.g. "24/08/2022 10:00:00") or just times ("10:00:00")

If it's just times, the formula fails because it's calculating a negative time because it doesn't know that A2 and B2 actually represent times on different dates

The correct thing to do would be to make the values dates & times (so that the formula can account for that properly)

If that is not possible, then you would have to incorporate another condition along the lines of, if the time in B2 is earlier than the time in A2, then assume that B2 represents the following day to A2 (or, assume that A2 represents the preceding day to B2)

Assumptions are never a wise step to take (e.g. what exceptions are there to this? What if there were a shift that spread across three consecutive days? How would you know if there is no date element to each value?)

But, if needs must, this formula should achieve that goal :

=IF(B2>A2,B2-A2-IF((B2-A2)*60*24>=(10*60),(1/24),IF((B2-A2)*60*24>=(5*60),(0.5/24),0)),B2+1-A2-IF((B2+1-A2)*60*24>=(10*60),(1/24),IF((B2+1-A2)*60*24>=(5*60),(0.5/24),0)))

As mentioned, though, I would strongly suggest incorporating dates to remove any ambiguity

 

[Ghana]

Works for me, are the values in A2 (start time) and B2 (finish time) dates & times (e.g. "24/08/2022 10:00:00") or just times ("10:00:00")

If it's just times, the formula fails because it's calculating a negative time because it doesn't know that A2 and B2 actually represent times on different dates

The correct thing to do would be to make the values dates & times (so that the formula can account for that properly)

If that is not possible, then you would have to incorporate another condition along the lines of, if the time in B2 is earlier than the time in A2, then assume that B2 represents the following day to A2 (or, assume that A2 represents the preceding day to B2)

Assumptions are never a wise step to take (e.g. what exceptions are there to this? What if there were a shift that spread across three consecutive days? How would you know if there is no date element to each value?)

But, if needs must, this formula should achieve that goal :

=IF(B2>A2,B2-A2-IF((B2-A2)*60*24>=(10*60),(1/24),IF((B2-A2)*60*24>=(5*60),(0.5/24),0)),B2+1-A2-IF((B2+1-A2)*60*24>=(10*60),(1/24),IF((B2+1-A2)*60*24>=(5*60),(0.5/24),0)))

As mentioned, though, I would strongly suggest incorporating dates to remove any ambiguity

Thank you for your reply I have done as you said and used date & time and now works fine. Thank you again for your help