We have a look at some of the basic date formulas and how to combine them to make more powerful date-based functions.
By Michael Barden
In the last edition of Office for Mere Mortals we looked at the basics of the date and time formats, the different ways they can and should be displayed, and how they are actually stored by Excel.
In this article we use the basic date formulas in Excel as building blocks and combine them to create more powerful date-based functions. You’ll be surprised how easily these tasks can be achieved.
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.$$PAGE$$
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)$$PAGE$$
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.$$PAGE$$
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 1st 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.$$PAGE$$
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.$$PAGE$$
LAST DAY OF THE MONTH
The DATE() function that specifies the last day of the current month works by specifying the date as the 0th 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 edition of Office for Mere Mortals, we’ll round off our knowledge of dates in Excel with a look at some more complicated date functions.
- Excel Online – changing date format
- Getting truly random numbers into Excel
- Dates in Excel 2003 – Part 3
- Dates in Excel 2003 – Part 1