Hi Jig,

Please see this tutorial: working with time in Excel. Under the heading: Shift Work Timesheets and Overtime

If you're still stuck, please come back and share your example Excel file so we can see the issue.

Mynda

Hi Mynda,

I was not able to figure it out so I will attach my excel file.  Thank you for helping!!! 

The serial number (as described in Mynda's video) for a duration of 2:30 is larger than that of a time of 2:20. Deducting the first from the last results in a negative value which Excel can't 'translate' to a time value. There's no such thing as negative time.

To overcome the problem you need to wrap the subtraction in a MOD function, like this:

=MOD(B40-C40,1)

This will result in a value of 0.993055555555556 that can be formatted as time to become 11:50 pm. This will work for all your subtractions (end time - duration). Also where a straight-forward subtraction gives the correct answer.

Like this one, never tried the MOD with time substraction.

Thanks Riny

Hello Riny,  Thank you so much for your help! This did resolve my issue!!!! 

Hello Riny,

How can I do conditional formatting between two times? I will attach my excel file. Thank you for  your help in advance!

Didn't notice your question until now. Perhaps the attached file helps you on your way, though I notice that your start and end times are not consistent. Thus, the formatting isn't always picked-up correctly.

Hi Riny,

It did work.  Thank you!