Tips for Excel, Word, PowerPoint and Other Applications
Math with Dates in Excel, Why there are only 38711 - TODAY() days before Christmas
Why It Matters To You
Got me, but I just used this the other day to re-calculate document destruction schedules and the cost of retaining boxes for the additional time. Actually, I'll circulate an interesting worksheet later on that combines several date/time functions and conditional formatting to create a schedule that automatically blocks out weekends and holidays. Seriously, there are tons of calculations involving dates, staff tenure, records retention, other stuff. Likewise, if you understand how to use date formulas, you can actually extract quite a bit of information out of a date value: day of the week, month, etc.
How To ...
Why do your dates sometimes get converted into funky 5 digit codes, what does it mean, and why is this useful to know? Microsoft developed it's own date format (the funky 5 digit code) so that Excel can run mathmatical operations off dates. They set 1/1/1900 as the base date. The code simply refers to the number of days since baseline. Decimals represent the part of the day.
I'll be using an example file, date_math.xls in the following examples.
Example 1 - Date Conversion
Here are two converters. Enter a date or 5 digit date code into the blue-shaded cell and it will convert the date for you. The only difference is how the cell is formatted. Cells formatted in a date or text format, will display as the dates we all love. Any other type of formatting, will result in a numerical value.
Changing Formatting
If you need to change the numerical format of a cell, the shortcut is CTRL+1, which will bring up the following dialog box. Select the Number tab, then a format. You can actually see what your cell will look like under various formatting schema in the Sample box. The graphic below shows you the different options for formatting dates.
Useful Formulas
You can access all of these from the Function Wizard:Date & Time category. I encourage you to poke around and take a look at what they do.
Example 2 - TODAY()
The only function I really use on a regular basis is TODAY(), which simply returns the current date. Since this automatically updates everytime a spreadsheet is opened, I use it to show when files were last edited. The synatax looks like this:
=TODAY()
Example 3 - DATEVALUE and WEEKDAY
A couple of other useful functions are DATEVALUE and WEEKDAY. DATEVALUE changes a text date into the MS-Excel date format and WEEKDAY returns a value of 1-7, which corresponds to the day of the week, starting with Sunday.
Here's what they look like:
The value of 3, corresponds to Tuesday
How is this used in practice?
Coincidentally, I've been using date math quite a bit because I'm in the middle of evaluating tenure limits on some temporary resources. With the new laws and regulations around co-employment, people are keeping closer track of how long temps work at a company. As such, every month I have to report how many temps are in place and how long they've been working. This can be set up using a simple function using TODAY() and the temps' assignment start date.
The first tenure calculation shown is =TODAY()-C24, which simply calculates the number of days between the current date and the assignment start date. You'll also notice that since I use a TODAY() function, this updates every time I open the report, so that I don't have to keep manually updating formulas.
Notes
| Last updated | 9/5/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips | None |