8 functions that will transform how you work with "Dates" in Excel

​Most of us are working with dates in Excel everyday, and knowing those useful functions will save you a lot of time and effort. 

​There are a lot of Date functions in Excel, but I will go through the most useful ones.

The first one is TODAY. If we type =today() in the formula bar we will get today’s date.

Now if you close this file now, and reopen it tomorrow, you will get tomorrow’s date.
​
Just note that my computer settings are set so that days display as MM/DD/YYYY. To change how your computer displays date, you can go to the control panel, Region & Language, and set how you want to display the dates

From this date cell, I can get the day of a specific date. I type =day and then reference the date that I want to get it’s day for.

If I want to get the month, what do I type? Exactly, I type =month and reference the date.

Year works the same way.

Now what if I want to get the name of the day (i.e. Sunday, Monday, etc…). Now a name is a text string, so the formula to convert the day to text is simply =text( then I reference the date (which is called value in the argument), and then put the format.
​
So days in Excel are formatted in dddd and each “d” represents a format type. If it’s 1 or 2 d’s, then it will show the day number formatted as text. 

If it’s 3 d’s it will show the first 3 letters of the day (Sunday will be shown as Sun).

If it’s 4 d’s it will show the whole day name.

For the month, it’s the same. But instead of putting d’s, we put m’s (m for Month).

This is not going to work for the year since years has no names.

Now imagine that you received a data set that has the days, months and years separated and you would like to get them together into a date.
In this case, we use the DATE function, where you type in Date(then you reference the year first, then month, then day.

This way you will be having a proper DATE to work with.

Now let me remind you of one thing. Excel only understand numbers and text strings. And it can do calculations over numbers.
For Excel, a date is a serial number.
​
To demonstrate this, let’s change the format of the date that we have to a number.

You will see that it turn to 42989. 

Now what is this number?
Ok before I tell you, let’s try something. Let’s type 1, and then format it to short date. 

So it shows 1st of Jan 1900. So this is the first date that Excel recognizes. 

And the 42989 means that 9/11/2017 is 42988 days after the first date in Excel which is 1/1/1900
And since dates are just numbers in Excel, this means we can do a lot of calculations over it.
​
Now let’s put another date, say 31st Dec 2017. And let’s see how many days are between this date and our date.
We just do a simple subtraction. If we subtract 9/11/2017 from 12/31/2017, we will get how many days are between the 2 numbers (or 2 dates).

Now let’s assume we want to know how many days are between the 2 dates excluding the weekends (because 111 typically includes the weekends).
There is an amazing function called NETWORKDAYS
So we type NETWORKDAYS, then we choose the second one which is the international one and I will tell you why in a second.

So we start by entering the start date, the end date, choose the weekend. You’ll see that we have a wide range of weekends, and you can choose the one relevant to your country.

​I will choose 7, which represents Fridays and Saturday. Then the last argument here is the holiday, but let’s ignore for now and come back to it later.
So now you see we have 80 days, which are the working days between 09/11/17 and 12/31/17.

Now the reason I chose the NETWORKDAYS.INTL is that the normal NETWORKDAYS function doesn’t have a weekend argument, and it just considers the standard weekend in the US which is Saturday and Sunday.

Now what if I want to see the number of days between 2 dates excluding weekends and public holidays?
This is where the last argument in the function comes into play.
So let’s input any set of dates and consider these dates as public holidays.

Then we choose this range in our formula and we get the number of days excluding holidays and weekends.

But wait a second, I should get 76 days since I have 4 days as public holiday (it’s 80 – 4 right?). Then how come?
Well, that’s right, but let’s see why this happened. Let’s use the TEXT function to convert those holidays into day names and see if any of them comes on a weekend.

​Aha. There is a date that comes on a Friday, and it’s a weekend in my NETWORKDAYS.INTL function, so the function was smart enough to not double count it as a holiday and as a weekend.
The last thing I would like to show you is adding to dates. Let’s imagine a scenario where you would like to add days to a specific date.
So we add 30 days to our date and we get a date that is exactly one month after our date.

But this date is exactly 30 days after our date, which means it includes the weekends.
Now what if we want to see a date that is 30 days after a specific date EXCLUDING weekends?
There is a formula for that, and it’s WORKDAY
So we type WORKDAY.INTL (again to have the option to choose between different weekends), type our start date, the number of days, weekend (this time we choose Friday only), and the range of holidays.

​So 30 “working days” after Sep 11th will be Oct 17th.

​Now go ahead and try out these functions and formulas. If you have any questions, feel free to post them here in the comments.