Having worked with Microsoft Excel for quite some time (almost over a decade), one thing that I’ve realized is that if one has to prepare the list of some “troublesome” Excel formula, the Excel date function would rank at the top as the simplest yet problematic excel function.
Other formulae don’t work because of the syntax issues. And that’s quite a logical reason. However, the Excel date formula can easily make you pull out your hair in agony once you realize that despite being accurate to the “letter”, the output is either you never expected even in your wildest dream or simply an error.
So in order to wrap our heads around the excel’s date function, and to figure out why it does what it does, this article will discuss the basic excel formula, how to manipulate it, how to calculate, add, subtract dates and get a meaningful data out of it.
Let’s dive in then.
Excel Date Function
The basic date formula in Microsoft Excel 2016 is written as:
=DATE(year, month, day)
All the input parameters in the function should be a number. For e.g. to get the output of 31 January 2018, the formula would become:
=DATE(2018, 1, 31)
Now, that’s the easy part. The real issues sprout when you have to manipulate and juggle around dates in different cells.
Say for e.g. you want to calculate age from date of birth in excel, or for how many monthly payments you need to make from a particular date. Getting Excel talk when it comes to the date function requires some work. So let’s see how to do it. But first, there’s some important knowledge that you need to have.
Date Format in Excel
One of the major reasons that the Excel Date formula does not function the way we want and ends up showing an error is because of the wrong “format”.
A person may very well enter a date in the cell but the excel registers it in the “General” or “Text” format. If that happens, the output is quite often an error. Similarly, there are some date functions which require their parameters in “Text” format and entering them in “Date”, “Custom” or “General” format leaves us scratching our head as to why the error keeps popping up despite there being everything right. So make sure that you know what data type or format does the excel date function accepts as its argument. By the way, this goes for all the Excel functions. Not just the date.
How Excel Keeps Track of Dates?
In order to troubleshoot a problem, it is necessary to know what is actually going on behind the curtains. So in terms of Excel dates, it is necessary that we know how excel keeps track of dates.
No matter how sophisticated excel is, there’s some pretty basic and grunt work going on at the back-end. You’d be surprised to know the elementary and simple approach used by Excel to keep tabs on dates at an individual level.
And what that approach is?
Well, the Microsoft has assigned a serial number to every single date starting from January 1, 1900 to December 31, 9999. These are 2,958,465 days or serial numbers.
So, just to understand it better and confirm what I’ve said is actually correct (just don’t believe anything blindly because someone said so. Test, test and test) let’s put my theory to test and for that, we will use the Excel’s DATEVALUE() function.
Excel DATEVALUE() Function
The Excel’s DATEVALUE() function takes in the date as an input and churns out the corresponding serial number assigned to the date. However, before using this formula there are some points that you need to be careful about:
- Be careful about the date format that you use. Not using the correct date format is the main reason things don’t work as they should despite using the correct syntax.
- The DATEVALUE() takes in the argument as a text string. So you need to enclose your date within the double inverted commas (“”). However, if you’re referring to another cell within the formula then there’s no need to enclose that cell reference within the inverted commas but the cell containing the date should be in TEXT format. We’ll see this in the example below.
Once we’ve got that out of our way, let’s solve an example to understand it in a much better way.
First, have a look at the image first.
As you can see the cell B2 with the formula, =DATEVALUE(“1900/1/1”) gives an output of 1.
Cell B3 gives an output of 14693. However, this time, instead of entering the date directly in the formula, we’re referring to it using the formula, =DATEVALUE(A3) where the date is in cell A3.
In the cell B4, we can see an error message, #VALUE!. This means that the values used are not in the correct format and this is indeed the case. The date entered in A4 is NOT in the TEXT format.
I hope you now understand how the Excel’s DATEVALUE function work.
What About The Excel Dates Before 1900?
Now, this brings us to an obvious question. What about the dates before 1900?
It’s a valid question and the answer to it is that there’s no serial number assigned to these dates. So you will have to make do with some advanced VBA just in case you need to manipulate the dates before 1900s.
I never said that Excel is without any flaws.
Using the Serial Number to Get the Year, Date and Month
Once you have the serial number, you can now extract the year, month and the date using the YEAR(), MONTH() and the DAY() functions respectively.
Continuing the above example. Let’s take a random date of March 23, 1940.
Instead of entering the serial number directly into the formula, we’ll be referencing it. This means that we will enter the serial number in column A and the formula in column B, we’ve already figured out the year, month and the date to which this serial number belongs.
In other words, the output would have been the same had we used:
//Output "3" - The third month of the year which is March
Note: Using the date as a text string in the above set of formula would have returned the same output. For e.g. =YEAR("1940/3/23"), =MONTH("1940/3/23") and =DAY("1940/3/23") would have produced the same result. Note the inverted commas within the parenthesis which means that the argument is in TEXT format. Why not go ahead and give it a try? See if it returns the same result.
Add Years, Months and Days to an Excel Date
Once we know how the Excel Date function works, we can now add years, months and days to any excel date and there’s not just one way to do it.
Add Years, Months and Days to an Excel Date Using the DATE() Function
In order to increment (or decrement) an excel date, a simple way is to add or decrease the serial number.
Let’s take another date. This time, it’s much simpler. January 1, 2010. The excel serial number for this date is 40179.
Now, once we know the serial number, we can easily figure out a particular date after “x” numbers of days. Let’s say we want to go to February 1, 2010. That’s 31 days after January 1, 2010.
In order to achieve that, we can use the following formula:
<strong>=DATE(YEAR(40179), MONTH(40179), DAY(40179)+31)</strong>
Notice the “31” at the end of the formula. We’re instructing Excel to go ahead and bring us the date 31 “Days” after our initial date.
What if we don’t want the whole date?
What if just want the year, month or date from the serial number.
Say, we want excel to fetch the month 1 month ahead of our original date above. We all know the answer. The month should be February and our excel should bring “2”, the second month. How can we achieve that? Here’s how:
Similarly, if we want to go ahead 3 years from our original date. How should we do that? It’s simple:
How to Get to the End of Each Month?
Say, you’re standing on January 1, 2010. You want the end of January 2010 which is, January 31, 2010. How’d you achieve that?
We’ve already worked out a formula which is:
=DATE(YEAR(40179), MONTH(40179), DAY(40179)+30)
//Output January 31, 2010
Ok, how about we go the end of the February 2016 from the start of February 2016?
The serial number for February 1, 2016 is 42401.
=DATE(YEAR(42401), MONTH(42401), DAY(42401)+28)
//Output February 29, 2016
While this works but it requires lots of manual work. You need to make sure that you add the right amount of days to get to the exact date. That’s laborious. Especially when you to work through an excel spreadsheet having hundreds and thousands of rows.
So what happens when you enter the wrong number of days?
Let’s give it a try. Let’s add 31 days in February 1, 2016
=DATE(YEAR(42401), MONTH(42401), DAY(42401)+31)
//Output March 3, 2016
Did you see what just happened here? Instead of showing an error and saying, “hey, February 2016 has only got 29 days”, the Excel keeps on adding until it adds 31 days. This brings us to March 3, 2016.
So you see what the issue here is. You have to be deadly accurate with your number of days when using this method to reach the end of the month.
So is there any workaround?
Yes, there is. It’s quite simple. Let me show you.
=DATE(YEAR(42401), MONTH(42401), 0)
//Output January 31, 2016
Did you just see what we did here?
We omitted the last argument (DAY()) from our formula and replaced it with the number, 0.
1 is always the first date of any month. So 0 automatically becomes the last day of the previous month.
Since the excel already knows the serial number 42401 belongs to the year 2016 and the month of February and we’re calling the day 0, this means that the excel needs to go back 1 day from the first day of the February 2016 which is January 31, 2016.
You can do this with any serial number. The last argument will tell the excel which day of the particular month to go to.
Interestingly, you can do the same with the months as well. Instead of being theoretical, let me show you a working example.
What if we need to go to…
The end of the 6th month of 2016 (June 30, 2016)
=DATE(YEAR(42401), 7, 0)
//Output June 30, 2016
Two years in the future of June 30, 2016 (June 30, 2018)
=DATE(YEAR(42401)+2, 7, 0)
//Output June 30, 2018
The end of February from 2013 to 2016 (including the leap year)
=DATE(YEAR(42401), 3, 0)
//Output February 29, 2016
=DATE(YEAR(42401)-1, 3, 0)
//Output February 28, 2015
=DATE(YEAR(42401)-2, 3, 0)
//Output February 28, 2014
=DATE(YEAR(42401)-3, 3, 0)
//Output February 28, 2013
So once you grasp the concept, it’s easy to play around and reach to your desired dates within a jiffy.
So that’s it for now. I had to discuss another important issue about calculating the number of days, months and years between two dates but owing to the limited attention span of our generation (the famous, internet generation) I’ve dedicated a whole separate article for it. You can read the article by following the link above.
Before signing out, I have a request for you guys. I’d love to hear out if this article helped you guys in any way. Use the comment section below for it. Also, do let me know if there’s an error above or something’s not working for you despite following the instructions.
A pat on the back keeps me motivated and a slap on the wrist keeps me on the right path. So make sure that you do not act shy when it comes to using any of these two options.