Working with dates

A lot of people get confused when working with dates. But they're really rather simple when you get to know them.

All dates and times are stored as numbers, the unit being days. Specifically, they're stored as the number of days since 1 January 1900. (There's a slight quirk that means that Excel counts one day too many, but that's not important right now.)

Today, 1 June 2011, is stored as 40,695. That's 40,695 days since the start of the last century. If you simply input a date, it will store it as the beginning of that day, or 00:00:00. If you want to reference midday on that day, you simply add 0.5 (half a day). And you can add any unit of time simply by adding it (in days) to the number of the date you're referring to.

So 2019 (19 minutes past eight in the evening) today is stored as 40695.846528 (or 40695 + 1219/1440 (1219 minutes gone out of 1440 in the day).

Even when you see a time displayed without a date, it always has a date associated with it. It's only its display settings that prevents it from being shown.

So if you want to add 12 hours to a timestamp, add 0.5. If you want to add three days and three minutes, add 3+3/1440. Excel will do the calculations and your display settings will show your time and dates accurate to the second. Job done.

A word of warning: take care when working across geographies. American dates are displayed as MM/DD/YYYY, whereas British ones are displayed as DD/MM/YYYY. Whenever there's a risk of confusion, display your dates with the month written out as a word or an abbvreviated word. This will ensure that people are happy when you produce that deliverable by 11 December, and that no one expected it on 12 November.

This entry was posted in Advice. Bookmark the permalink.

12 Responses to Working with dates

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.