Previous Story
Calculating Hours Difference of Time in Excel Spreadsheets
When working with Time in Microsoft Excel you may have noticed that there is a problem when you subtract certain numbers. Excel gives you an error, but it doesn’t look like an error. Excel just shows you a lot of hastags (Pound symbols) like this: “#############”. And they are never ending. Most errors display #N/A, #ERROR or #VALUE. So what is going on here?
Negative Time
Now you may or may not know, but time moves on and not back (at least not until they develop that time machine). However, definitely in Excel terms, time is only positive. So if you subtract 2 values of time and they are negative, you will see the error like this: “#############”.
Here is a sample when we subtract a later time like 1PM from Noon as you see below:
| A | B | C | |
|---|---|---|---|
| 1 | Time | ||
| 2 | Start Time | 12:00 PM | ########### |
| 3 | End Time | 1:00 PM |
Worksheet Formulas
|
Techniques for Dealing with Negative Time
There is a simple tip and trick to get around the strange time error that you are seeing when you subtract time in Excel. All you have to do is to change the Number Format of the cell where you are subtracting time.
Yes, that is it. Notice that I have the same formulas below in cells C2 and D2 however, one cell is showing an error and the other is showing a number. The only difference is the number format that you can change from the Excel Home Ribbon.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Time | Number | ||
| 2 | Start Time | 12:00 PM | ########### | -0.04167 |
| 3 | End Time | 1:00 PM |
Worksheet Formulas
|
Determining Time Worked by Subtracting a Lunch Hour
So lets take it one step further. If you need to subtract time to calculate something like the total time worked today minus the non-paid lunch hour, then use this technique.
If we subtract time as per the previous answer, we will not get the answer we desire as Excel treats an hour as a decimal. It works really well, but your answer would be “0.04167” and not 1.
To get around this, you may want to use the HOUR Function. This hour subtraction formula would wrap both cell references in HOUR Function look like this:
| A | B | C | |
|---|---|---|---|
| 1 | Hour Function | ||
| 2 | Start Time | 12:00 PM | -1.00000 |
| 3 | End Time | 1:00 PM |
Worksheet Formulas
|
Video Demonstration
Check out the Video Tutorial here:
Hopefully this helped you think of time in Excel in a different way and help you with your time issues. If so, please leave me a comment below.
Steve=True
If it is important that it looks like a timestamp you could use a formula like this for presentation (but not for further calculations):
=IF(D7<D8,"-","")&TEXT(ABS(D7-D8),"[h]:mm:ss")
Thanks for the comment. Good technique. Thanks!
great one steve as always. KUDOS
Text(b2-a2,”-h:mm:ss”)
B2= start date
a2 = end date
or wrap this in IF formula
Thanks Ravi