Comfy Computing Ltd

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. Here, 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

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.

THE DIFFERENCE BETWEEN TODAY() AND NOW()

The =TODAY() function returns the current date only based on your computer system's calendar.

The =NOW() function returns both the current date _and_ time based on your computer system's calendar and clock.

Try typing them into separate cells in a worksheet and you will see they are displayed differently - one with time and one without.

NOW() = 12 Mar 2007 23:05

TODAY() = 12 Mar 2007

The difference may not seem like much, but keep in mind that serial date representations are also used in date and time comparisons, here's what Excel is really storing for the above examples:

NOW() = 39153.96181

TODAY() = 39153

Strictly speaking TODAY() returns the date and the time at midnight e.g. 12 Mar 2007 00:00:00 .

Therefore NOW() does not equal TODAY() because the numbers are not exactly equal - but if you're just looking at the date component that doesn't seem correct.

COMPARING DATES

If you want to compare dates, make sure you are using the right formula in the right situation - compare dates with dates and times with times.

For example, you want to compare a date with the current date and you use the formula:

=IF(NOW() = DATEVALUE("12-Mar-2007"), TRUE, FALSE)

The formula returns FALSE even when today's date is 12-Mar-2007. This is because the NOW() formula returns both date and time, while the DATEVALUE() function returns the date only.

Therefore, it will only return TRUE when the date is 12-Mar-2007 and the time is exactly 00:00:00.

Obviously in this case you want to replace the NOW() function with the TODAY() function. Since both the TODAY() function and DATEVALUE() function generate the default time of 12:00:00AM, this will only check the date value of each function against each other and will return TRUE if today's date is 12-Mar-2007. Therefore the right formula is as follows:

=IF(TODAY() = DATEVALUE("12-Mar-2007"), TRUE, FALSE)

In this example, we used the DATEVALUE() formula to specify that a string was meant to be interpreted in the logical test as a date. For those who are familiar with some computer programming, it is similar to the concept of "type-casting". In this case you are casting a string into a date format.

If you enter the date 12-Mar-2007 into cell A1, then the formula can reference this cell and no type-casting with the DATEVALUE() formula would be necessary. The formula then becomes:

=IF(TODAY() = A1, TRUE, FALSE)

YEAR(), MONTH() AND DAY() STRINGS

Excel also provides some functions to extract certain information from a date. If you always want to display the current year as part of a Copyright notice in a particular cell of a worksheet, type in the following formula:

="Copyright " & YEAR(NOW())

The current result is Copyright 2007. Next year it will change to Copyright 2008.

The MONTH() and DAY() functions work in much the same way. They extract the respective month and day portions from a referenced date and produce a string. The formula =MONTH(DATEVALUE("12-Mar-2007")) will produce the string value "3" as March is the third month.

The NOW() and TODAY() functions are interchangeable when placed within a YEAR(), MONTH() and DAY() function, as these functions are solely focused on their respective part of the date. They completely ignore any time value.

BASIC DATE ARITHMETIC

The =DATE(year, month, day) formula creates a date out of the 3 specified parameters. To create a function that specifies the date of the first day of next month, then we can combine this new formula with some of our basic functions as follows:

=DATE(YEAR(NOW()), MONTH(NOW()) + 1, 1)

This formula provides the DATE() formula with the current year, the next month (current month + 1) and the 1^st day.

If you want to find out the date 100 days from today, simply add 100 to the day value of the current date as follows:

=DATE(YEAR(NOW()), MONTH(NOW()), DAY(NOW()) + 100)

Finally, if you want a basic way of displaying the number of days until a certain project is due (or until a holiday like Christmas day), try using the following type of formula and then changing the cell formatting to "General":

=DATEVALUE("25-Dec-2007") - TODAY()

If the cell formatting is 'Date' then the number of days will appear as a seemingly incongruous date in the early 1900s - when you change to 'General' you'll see the number of days between today and your next batch of gifts.

Often a simple calculation of the difference between two dates in days isn't enough - in the next issue of Office for Mere Mortals we'll look at some more useful methods.

MAKING A DATE - TIPS AND TRICKS

The DATE() function also has a few tricks that can be useful if you know what you're doing. Some values that you might expect to return an error are accepted, but they can also give you results you might not expect.

The MONTH() value is actually a positive or negative integer representing the month of the year. The traditional values range from 1 to 12 representing January to December. If you supply the DATE() function with a month value greater than 12, it adds that number of months to the first month in the year specified. For example, DATE(2007,15,4) returns the date: 4-Mar-2008.

If you supply the DATE() function with a month value that is less than 1, it subtracts that number of months plus 1 from the first month in the year specified. For example, DATE(2008,-4,15) returns the date: 15-Aug-2007 (five months before 15-Jan-2008).

A month value of 0 gives December in the year before the one specified. A month value of -1 gives November in the year before the one specified.

If you supply the DATE() function with a day value that is greater than the number of days in the month specified, it adds that number of days to the first day in the month. For example, DATE(2008,1,37) returns the serial number representing 6-Feb-2008.

LAST DAY OF THE MONTH

The DATE() function that specifies the last day of the current month works by specifying the date as the 0^th day of next month as follows:

=DATE(YEAR(NOW()), MONTH(NOW())+1, 0)

For example if NOW() is in March then MONTH(NOW()) + 1 returns April and the full result will be 31 March in the current year.

Changing the final 0 to -1 will give the penultimate (second-last) day (e.g. 30 March). Similarly -10 will return ten days before the end of the previous month (21 March).

In the next section, we'll round off our knowledge of dates in Excel with a look at some more complicated date functions.

In the previous section , we used the basic date formulas in Excel as building blocks and showed how to combine them into more powerful date-based functions.

In this part we round off our date functions in Excel topic with a look at custom date formats, the NETWORKDAYS() function, and an in-depth discussion on sharing date-related worksheets between Mac and Windows.

CUSTOM DATE FORMATS

There are times that you want to see the displayed value of a date as something other than the actual date or the generic date formats. For example, rather than a cell in a worksheet displaying today's date as 03/15/2007, you might want to see the value as either "Thursday", "Mar-07", "15 Mar" or "Thursday, 15 March 2007". All of these formatting combinations and many more can be achieved in a couple of different ways.

The first way is to set the format on the actual cell in question. To do this select the cell or range of cells, navigate to "Format | Cells" and select the "Number" tab. In the "Category" pane, select "Date" and scroll through the "Type" pane for common examples of how dates will appear.

If you can't find the date format you want in the "Date" category, choose the "Custom" category and you can type in your own date format in the "Type" text box as a combination of m's (month), d's (day) and y's (year).

Custom Date Format Creation

The following list from Microsoft describes each of the date format building blocks that can be used in a custom date format:

• m - Display the month as a number without a leading zero
• mm - Display the month as a number with a leading zero when appropriate
• mmm - Display the month as an abbreviation (Jan to Dec)
• mmmm - Display the month as a full name (January to December)
• mmmmm - Display the month as a single letter (J to D)
• d - Display the day as a number without a leading zero
• dd - Display the day as a number with a leading zero when appropriate
• ddd - Display the day as an abbreviation (Sun to Sat)
• dddd - Display the day as a full name (Sunday to Saturday)
• yy - Display the year as a two-digit number
• yyyy - Display the year as a four-digit number

So assuming our cell contains the date 03/15/07 we can create a custom date format to display our date in any number of ways (not limited to the following):

• "ddd" produces "Thu"
• "dddd" produces "Thursday"
• "dd-mmm-yyyy" produces "15-Mar-2007"
• "dd, mmmm-yyyy" produces "15, March-2007"
• "mmm-yy" produces "Mar-07"
• "dd mmm" produces "15 Mar"

CUSTOM DATE FORMATS USING TEXT()

Another way to display a customized date is to use the TEXT() function along with a recognizable pattern of the m's, d's and y's mentioned above.

For example, if you want a cell to display the current day of the week, try using the formula =TEXT(TODAY(), "dddd"). If today's date is 15-Mar-2007, then the result will be displayed as "Thursday".

You can also specify a particular date within the formula itself. Typing =TEXT(DATEVALUE("15-Mar-2007"), "dd mmmm") in a cell will display the result as "15 March".

If you have a date referenced in a cell (e.g. A1) that you wish to display in a more detailed fashion elsewhere, try using a formula like =TEXT(A1, "dddd, dd mmmm yyyy"). With "15-Mar-2007" in cell A1, the result would be displayed as "Thursday, 15 March 2007".

THE POWER OF NETWORKDAYS()

One date-based Excel function that is worth its weight in gold is NETWORKDAYS(). The syntax is as follows =NETWORKDAYS(start_date, end_date, holidays). This function returns the number of whole working days between two dates - excluding weekends and any specific dates you have typed in the "holidays" parameter.

The "holidays" parameter is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.

It is possible to enter starting and ending dates directly as serial numbers, as text, through a cell reference, or by using the DATE or DATEVALUE functions. You can also enter a single holiday date as the holiday parameter in this way.

For example, say we wanted to find out how many work days there are between the 19^th of March 2007 and the end of the year - only excluding Christmas Eve and Christmas Day as the designated holidays. The following formula can be used to produce a result of "204" work days (taking particular note of the syntax used to include multiple holidays):

=NETWORKDAYS(DATEVALUE("19-Mar-2007"), DATEVALUE("31-Dec-2007"), DATEVALUE({"24-Dec-2007","25-Dec-2007"}))

If you created a list of holidays in cells A1 through to A10 and wanted to know how many work days there are between now and Christmas Day (excluding your range of holidays), you could use the following formula (taking particular note of the syntax used to include a cell-reference range of holidays):

=NETWORKDAYS(TODAY(), DATEVALUE("25-Dec-2007"), A1:A10)

The NETWORKDAYS() function is particularly useful for giving you the actual number of available work days left before a large project is due. Once you take the weekends out of the equation, you'll begin to realize just how little time you really have left!

You could also theoretically use NETWORKDAYS() to count down the number of work days until you retire (however frighteningly large).

NOTE: If the NETWORKDAYS() function returns a #NAME error you will need to install the "Analysis ToolPak Add-In". To do this, go to "Tools | Add-Ins", select the "Analysis ToolPak" check-box and click "OK".

THE 1904 DATE SYSTEM AND CROSS PLATFORM SHARING

In our first "Dates in Excel" article, we briefly mentioned the 2 Jan 1904 base date system that can be enabled under "Tools | Options | Calculations" as an alternative to the default 1 Jan 1900 base date system. We didn't mention why the option is there or what pitfalls there are in using the system.

Office for Mere Mortals reader Char R. wrote in with some interesting points on the subject. When Microsoft originally made Excel for Mac there was a problem that prevented it from correctly using the 1 Jan 1900 base calculation date, hence the use of a 2 Jan 1904 base date. This means that when you type the serial date number 1 in and format it as a date, Excel for Mac displays it as 2 Jan 1904 12:00AM, while Excel for Windows displays it as 1 Jan 1900 12:00AM.

Since Excel for Mac defaults to the 1904 system and Excel for Windows defaults to the 1900 system, sharing worksheets between Mac and Windows can be problematic.

According to Microsoft: "If you transfer files from Excel for Mac to Excel for Windows, this difference in the date systems should not cause a problem, because the date system is stored in each file. However, if you copy and paste between files with different date systems that originated on different platforms, dates may be displayed four years and one day away from their correct date."

Since Excel in both Windows and Mac can be changed to use either date base system, the best practice when sharing a worksheet across platforms is to make the recipient aware of what format the dates are stored in. They can then change the system stored in the worksheet to match their default system. Similarly, when receiving worksheets across platforms it is best practice to check how the dates are stored and change it to match your default system.

Since Mac users often live in a world of compromise where Microsoft is concerned, they may find it more convenient to clear the "1904 date system" checkbox ("Edit | Preferences | Calculation") when sending worksheets to Windows users. They may even wish to use the 1900 date system by default, although this may then create issues when sharing worksheets with Mac users who still use the 1904 system and so on. There really is no perfect solution.

For more information on the 1900 and 1904 date systems, read the Microsoft KB article.

CALCULATING AGES BEFORE 1900

Excel enters dates prior to 01/01/1900 as plain text. This Microsoft KB article provides a macro for a function that calculates the age of someone or something created before 1900. It works with dates entered as text beginning with 01/01/0001, normal dates, and can handle dates when the starting date is before 1900 and ending date is after 1900.