O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #38. Hack Excel's Date and Time Features

Excel's date and time feature is great if you're creating simple spreadsheets, but they can cause problems for more advanced projects. Fortunately, there are ways to get around Excel's assumptions when they don't meet your needs.

Excel (by default) uses the 1900 date system. This means the date 1 Jan 1900 has an underlying numeric value of 1, 2 Jan 1900 has a value of 2, and so forth. These values are called serial values in Excel, and they enable you to use dates in calculations.

Times are very similar, but Excel treats times as decimal fractions, with 1 being the time 24:00 or 00:00. 18:00 has a numeric value of 0.75 because it is three-quarters of 24 hours.

To see the numeric value of a date and/or a time, format the cell containing the value as General. For example, the date and time 3/July/2002 3:00:00 PM has a numeric value of 37440.625, with the number after the decimal representing the time, and the 37440 representing the serial value for 3/July/2002.

Adding Beyond 24 Hours

You can add times by using the SUM function (or a simple plus sign). Therefore, =SUM(A1:A5) would result in Total Hours if A1:A5 contained valid times. There is, however, a big "Gotcha!" Unless told otherwise, Excel will not add past 24 hours. This is because when a time value exceeds 24 hours (a true value of 1), it rolls into a new day and starts again. To force Excel not to default back to a new day after 24 hours, you can use a cell format of 37:30:55 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required