Correcting “Wrong” Time Calculations in Excel

Posted: April 30, 2013 by MaxFraudy in Excel
Tags: , ,

Working with dates and times in Excel is a tricky business. If you don’t know how Excel stores dates and times and if you add them, you might end up getting unexpected results. e.g.

Apparently Wrong Result

Apparently Wrong Result

At the surface of it, it looks like the addition is giving a wrong result. In reality, however, this is just the format or the representation of the result that is misleading. Here’s how – Excel stores dates as numbers, i.e. no. of days since Jan 1,1900. So, Jan 5, 1900 would be stored as 5. Similarly, times are stored as fractions. 0.5 would be 12pm and 0.75 would be 6pm and so on.

What happens when you add times is that if the sum exceeds 1, then the integer part of the result is treated as date and the fraction part as time. To overcome this, you need to TELL Excel that you want sum as hours (or minutes, or seconds, as per the case). This can be done by simply changing the format of the cell.

Applying Custom Fomat

Applying Custom Fomat

The square brackets let Excel know that you want your result in hours. Similarly for minutes and seconds. The result now is:

'Correct' Summation

‘Correct’ Summation

There is a lot more that you can do using Custom Formats. More on this later.

Advertisements

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