Dates are a special type of number format in Excel. Behind every date is a number and we can use regular number operations on dates to change them. However the number isn’t what we would think, for example 13/08/2014 would not be stored as 13082014, or something else recognizable for what the date is. Instead it is stored as a number specific to Excel, so the date 13/08/2014 is actually 41864. Going the other way, the number 1 for Excel is actually the date 1/1/1900.
In this article we will both use this number system and learn some handy functions to use actual date parts (e.g. month, year):
- Basic date formats
- Date and Time
- Subtracting days
- DATE function
- YEAR, MONTH, DAY functions
- TODAY function
Basic Date formats
One of the quickest ways of telling Excel that the current cell is a date, and should be formatted so, is using the quick format option. I have covered this option before in the article Formatting numbers in Excel when dealing with currency, and percentages. It is located on the Home ribbon in the Number section, and some of the options there are the date formats
There is three options for dates actually: the long date, the short date, and time. Short date will format the date as 1/1/1900 while the long date will format it as Sunday, January 01, 1900:
The long date probably is rarely useful but shows some of the options you have for formatting. Remember that you started with just a number (1 in this case). Time will set the Time format as in 19:35. We will cover time shortly below.
If you want to get more choice with dates you can access the Format Cell menu by either right clicking on the cell and selecting Format Cells… or by clicking on the the little arrow at the bottom of the format numbers area (see Formatting numbers in Excel for more details)
As you see above, this window gives you a lot more options, from really short format of just day/month to more detailed ones. In addition you have more localization options to ensure compatibility with your geographic location.
Date and Time
While a more detailed look at time is out of scope for this article, I wanted to talk briefly about the relationship of date and time. Time like Date is a format we can apply to numbers. So just like dates time is simply some Excel number formatted to look like we want (e.g. 13:34). While we saw that dates are whole numbers, time is actually somewhere between 0 and 1, with 0 being midnight and 1 going back to midnight again (of the following day). So we can use this for example to calculate half a day – i.e. 0.5 = 12:00 or 1 hour is 1/24 = 0.041667. Try it, type in =1/24 into a cell, and format it as time using quick formats, you’ll get 1:00:00 AM:
As I mentioned before 1 = 0:00 which is the same as 0. That is because we are at midnight the next day. Recall we said that 1 = 1/1/1900, in reality you can also show it as 1/1/1900 0:00, so 1.25 is 1/1/1900 6:00. At 2 the clock resets to 0:00 again and we get into 2/1/1900 (Jan 2) midnight.
So with the above in mind, if 1 is Jan 1st, 2 is Jan 2nd etc, then we can clearly see that adding or removing 1 to a date will increase or decrease the date by 1 day. This is really useful if we want to show recurring days, say every Monday, or every two days, we simply keep adding that many days to the date and get a new date:
One of the most common ways I use this is when I have a weekly report and I need to have the headers be say the last 6 weeks, instead of typing it in each week, I simply update one then set the next one to be 7 days prior to the previous one (or after whichever):
Only the the most recent week is the actual value, the rest are automated formulas like the one in column G, they just take the cell to the right and subtract 7. You set up the formula in column G and then just copy paste into the rest of the columns (see What Happens when you copy or cut and paste in Excel).
Weeks are simple as they are always 7 days but months and years can be tricky because of different length months and leap years. So we cannot simply rely on a magic number that will move us up by exactly a month. However we can combine the DATE function with MONTH and YEAR functions to give us that ability
DATE allows you to specify a specific date almost as if you are typing it in. It takes Year, Month, Day – in that order – as parameters and converts that into the Excel numeric value equivalent to that date:
As you see in the example above we can build up a date from values in other cells. So using this, assuming you know your start date, you can set the formulas to automatically show months in order like we did with weeks. All we need to do is to use a ‘helper’ row:
Only the column H in the helper row has actual values,everything else is formulas. The helper row cells except column H are simply pointing to one right and reducing by 1. While the actual month start header is using the date function with day and year predefined but gets month from the row above. We could do the same for year as well (another helper row). Remember that helper rows can be either hidden, white out (set background and text to the same colour), or if simply copying to power point or word just set it outside your actual table (I prefer above separated by one row).
But we can do better, what if we could simply ask the previous cell what was it’s month, day and year, and simply deduct a month, i.e. we get rid of the need for a helper column. Well there are functions for that
YEAR(), MONTH(), DAY()
DATE function lets us compose a date from it’s components, but to go the other way, i.e breakdown a date into its parts we have three separate function. YEAR function takes a date and gives you the year, MONTH function the month, and DAY the day. So if we go back to the previous example, we can simply ask the date in column H what are your parts and deduct one from the month (I flipped the view to make the function easier to read):
Cell B8 (with Sept 1st) has the actual date, then each cell above looks at the cell below and breaks down the date and reassembles it, just with one month less. So we set the year the same, the day the same, but we take the month and then take 1 away from it.
Lets take a look at a complex example to put it all together. The report has the last 4 months, this month last year, and year to date this year and last year And lets say everything is in descending order. i.e. we show the most recent column month/year first then the rest. So in other words we want a report with the following columns:
- 4 columns for last 4 months
- Last month – last year
- This year
- Last year
We can automate the dates, so that we only need to change the most current date and everything will else will update on its own. Here is the final product with the formulas below (click to view full size):
Let’s go through the functions. In D3 we have the actual last month date. So if the report is for September 2014 then this will go in this cell. Next cell over (E3) is the month before that. The formula is:
The outside function is DATE Recall it takes in 3 parameters: year, month, and day. For the year we use the YEAR function to get the year of the of the report month (i.e. the YEAR of the date in D3). For the month we start off the same, just get the month of the report month, but then we deduct 1 to get the previous month. So instead of September the month is August. If we wanted to go in reverse (and increase months) we would add one. Then for the day we do the same as the year. We use the DAY function to get the day of the month. When dealing with months if you don’t care about the date (either it is always 1, or we format the date to just show the month), we can just hard code the date as 1 instead of the formula (i.e. the formula will read =DATE(YEAR(D3),MONTH(D3)-1,1). For the rest of the last 4 months we simply copy over the formula over and it will automatically update to look at the month to the left.
This method also works when we have to go back to a previous year. If, for example, our report date (value in D3) is 1/1/2014 then it would still work and correctly calculate 12/1/2013/ I.e. month -1 of 2014 is month 12 of 2014. We can use this to help us in various calculations, For example when looking for month end date (as it varies form 28 to 31) we can go to 1st of next month and get the day -1 of that month.
Now lets look at the next portion – this month last year:
The formula looks very similar to the previous months’ formula but with small change, we keep the month as is, but we take one away from the year. Remember we are pointing to D3 the original report month, not to the column next to Last Month Last Year. In other words we want to get the month and day of the most recent month, but last year.
The final parts are the year to date columns. For this report I have chosen to simply represent the year to date header as the year number. So all we need to do is get the year the report is for:
Note that again I am pointing to the most recent month, if we are doing year to date would make no sense to look at months before in case they are in a previous year. However in some situations you might want to do that. For example if you want to show YTD only once a quarter has passed then we simply point to the three months ago column, As soon as that becomes January and switched to the new year you know you have at least three months passed (your most recent month will be March), otherwise your header will show the previous year
To get the year before we don’t do anything fancy with dates, we simply take one away from the current year. Since we are showing a number not a date already, we can do regular arithmetic. Speaking of regular arithmetic, why not calculate the year before by simply taking away 365 days from current date? Leap years is why, some are 365 but some are 366 days so it won’t always work. You can get away with it sometimes but need to keep a note to update this for leap year
The last function I wanted to touch on is TODAY(). This is a different kind of function that what we’ve seen before. Instead of manipulating some input, it takes no parameters and returns today’s date. Note that the date is set by the system so if for some reason you system date is not current date, then Excel will show the incorrect date as well. To use it simply type TODAY() as such:
We can treat the result of the function like any other date, so you can use all the functions above to break it down and rebuild it. For example we can automate the headers for our monthly report completely. Assuming we always produce the report after the month is over but before the end of the following month we can use the current date to get the month before and then the rest of our formulas will do the work.
Recall before in cell D3 we had an actual date value, we are going to replace the value with a formula that gets the date of the 1st of the month the of the previous month:
So instead of actually typing in 9/1/2014 we used the formula that calculated the same. Most of the formula should be familiar by now, it is the same as we did for previous months. Except instead of referencing a cell we are referencing the TODAY value. We take the DATE() function to create a date; the first parameter is year so we use the YEAR of TODAY; for month we get the month of TODAY using MONTH and take 1 away to get to previous month; the last parameter is day which we hard code as 1 so we always have the first. Pretty easy and like before works for going from backwards from January to December.
Note TODAY() always recalculates so if you have that in a report and go back a few months later, TODAY will recalculate the headers to actual TODAY date. If you need to keep reports for historical purpose always hard code any values with TODAY() after the report is finalized. I.e. save a copy where you type in the actual value not the formula so you can go back if needed
By now you should be able to do some pretty nifty things with dates as well as apply some formatting as needed. We looked at what dates are and how to change their presentation. We explored how to manipulate dates using simple math as well as how to break it down and rebuild it. Finally we looked at a more complex example with dates and automated it with the TODAY() function. There is a lot more to dates but this should get you started. Next time we will look at some more functions for dates as well as some more formatting techiniques