Calculating Hours Difference of Time in Excel Spreadsheets

Calculating Hours Difference of Time in Excel Spreadsheets

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





Cell Formula
C2 =B2-B3

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.

Change Number Format When 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

Cell Formula
C2 =B2-B3
D2 =B2-B3

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.


SPECIAL - SAVE 10% until July 20th. Use code EDT.


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

Cell Formula
C2 =HOUR(B2)-HOUR(B3)

 

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





4 COMMENTS

  1. 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")

LEAVE A REPLY

Please enter your comment!
Please enter your name here