Correcting “Negative” Time Difference Calculation in Excel

Posted: May 14, 2013 by MaxFraudy in Excel
Tags: ,

As we have discussed in an earlier post,Excel stores times and dates as numbers. We have already seen how to deal with summations of times so that it is displayed correctly. Now we’ll discuss subtraction.

Suppose I have the data for some tasks, i.e. their planned start time and actual start time, and I want to know the difference between the two. The tasks which started on time will give 0 as result, the ones which start early will have a negative value and the ones starting late will have a positive number as the result. You will notice that the places where the result is negative, Excel displays ‘####’ as below and widening the column does not make any difference.

Negative Time - 1900 Date System

Negative Time – 1900 Date System

The problem here is that Excel does not display negative hours by default. If, however, you wish to see the results then you need to change the date system that Excel uses. To do this, go to Office button -> Excel Options -> Advanced tab ->Check ‘Use 1904 as Date System‘. Now you will be able to see negative values as well.

Negative Time - 1904 Date System

Negative Time – 1904 Date System

Do remember that changing to this system will change the dates in your workbook (if you have any) by 4 years. Therefore, take a call as to how you want to deal with it.

 

 

Advertisements
Comments
  1. JRAJU says:

    Hi, I like your solution. But how to get the negative time not only in time but also in days. For example, deducting todays time from yesterday time (for ex. 12/15/2013 08:12 aM-12/16/2013 06:00 PM

  2. Can we show that negative time difference in Line graph… I have been trying but time doesn’t go negative..

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s