Log In
Register
Home
April 15, 2023
Offline
Hello, I have a question regarding time subtraction from AM - Hrs = PM
1:00:00 AM - 02:00 (hours) = 11:00:00 PM
It's giving me an error and I don't know what formula to use to get result 11:00:00 PM
Thank you for your help!
See Answer
July 16, 2010
Offline
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
April 15, 2023
Offline
Hi Mynda,
I was not able to figure it out so I will attach my excel file. Thank you for helping!!!
Moderators
January 31, 2022
Offline
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.
Answers Post
Trusted Members
October 17, 2018
Offline
Like this one, never tried the MOD with time substraction.
Thanks Riny
April 15, 2023
Offline
Hello Riny, Thank you so much for your help! This did resolve my issue!!!!
April 15, 2023
Offline
Hello Riny,
How can I do conditional formatting between two times? I will attach my excel file. Thank you for your help in advance!
Moderators
January 31, 2022
Offline
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.
1 Guest(s)
