Dates in Excel 2003 - Part 1

Office for Mere MortalsLast updated: 17 July 2018

We run through the basics related to date and time formats in Excel 2003.

By Michael Barden

Working with dates in Excel is a common task:

Are you trying to determine the number of days between two dates?
Do you need to know the date that is X number of days in the future?
Do you want to find out how many work days there are before Christmas, or until you retire?

Date formulas in Excel can be extremely useful in a variety of projects – large and small alike. In this series of Office for Mere Mortals articles we have a look at some of the basic date formulas and how to combine them to create more powerful date-based functions. But to start, we’ll have a look at how dates and times are stored in Excel.

Excel tries to hide the technical workings from you – but when things go awry you’ll be able to fix it easily if you understand what happening.

HOW DOES EXCEL STORE DATES AND TIMES?

Regardless of how you have formatted a cell to display a date or time, Excel will always store it internally in exactly the same way.

The format Excel uses is known as the “Serial Date” representation and it appears as a decimal number. The integer portion (up to 6 digits) represents the number of days that have passed since 1-Jan-1900. The decimal fraction (also up to 6 digits) represents the time as the fractional portion of a 24 hour day.

For example, the serial date of 17.25 represents “17-Jan-1900 06:00”.

The integer part is 17 because 17 days have passed since the counting began.

The decimal fraction is 0.25 because 6:00AM represents exactly one quarter of a 24-hour day.

Using these same principles, the serial date of 39140.61576 represents “27-Feb-2007 14:46”. That means that 39,140 days have passed since 1900 began. It also means that 61.576% of the day has passed.

To get a grasp of serial date and time representation, try entering a date like “28-Feb-07 12:00” into a worksheet cell. Excel should recognize this as a date and time combination. Select the cell and navigate to “Format | Cells” in the menu.

The Format Cells Dialog image from Dates in Excel 2003 - Part 1 at Office-Watch.com

Choose the “Number” tab and select the “General” category. This will display the unformatted serial date version of the data that Excel stores.

The result of our example is 39141.5. This makes sense as it is 1 day more than our 27-Feb-07 example, and the time is 12PM which is 50% of the way through the day.

If you enter a date without specifying a time, then the serial representation will assume that the time is 00:00 (12:00AM) and hence the decimal fraction part after the decimal point is always 0.

If you enter a time without specifying a date however, Excel does NOT assume the time is related to today’s date. Instead, the integer part of the serial representation will be set to 0 as a way of saying that no date has been specified.

Excel cannot represent a date up to and including 31-Dec-1899. You can enter the ‘pre-1900’ date as text but no date arithmetic.

At the other end – Excel will work with dates up to 31 Dec in the year 9,999.

Note: Most Excel worksheets use 1 Jan 1900 as the base date but there is another system using 2 Jan 1904 – you can enable that under ‘Tools | Options”.

HOW ARE DATES SORTED?

Yes you guessed it – dates are sorted based on its serial date representation. This is how Excel sorts your dates in chronological order easily regardless of the format of the date. This can be very confusing and frustrating for people who aren’t aware of the underlying method of storing dates in Excel, and expect it to work the same way as your common string sorting.

CONVERTING TEXT TO DATES

Recent versions of Excel are much smarter about converting a date you type into a serial date that Excel can work with.

For example type ‘ 11 Nov 07 ‘ or ‘ 11-Nov-2007 ‘, and Excel should convert that into a date.

Exactly how Excel converts a date depending, in part, on your date settings in Windows (Control Panel | Regional Settings). ‘ 10-11-07 ‘ can be either 10 Nov 2007 or 11 Oct 2007 depending on those settings.

We suggest you choose a date setting that uses words for the month, so you can see immediately that Excel has converted what you’ve typed into a valid date – and even better the correct date!

According to Microsoft, you can type a number with a slash mark (/) or hyphen (-), it may be converted to a date format. If a number contains a colon (:), or is followed by a space and the letter A or P, it may be converted to a time format.

But you need to ensure that the typed date is converted into an Excel date, otherwise it will remain as text and your formulas won’t work.

Those are the basics – in the next issue we’ll start to look at date arithmetic. There are simple ‘days between two dates’ things to do (you can probably figure that out from the above) but on planet Earth you have to deal with weekends, public holidays, personal vacations and other realities. Then there are all sorts of other nifty tricks.