Excel Date Functions

Five Date Functions

Excel's Date Functions are covered extensively in our Excel classes in NYC. For those outside New York, find and compare the best in-person Excel classes near you or live online (virtual) Excel classes.

Let's start with the first of five date functions, the DAYS function. The DAYS function will tell you the number of days between any two dates, the end date and the start date. 

NETWORKDAYS. This will tell you the net workdays between any two dates. Basically days that do not include Saturday and Sunday, hence net workdays, since you don't work on a Saturday or Sunday. 

WEEKNUM. This will tell you what week you're currently in out of 1 to 52. There are 52 weeks in a year, and if you enter a current date, it'll tell you what the current week is. 

EO, end of month. This function basically tells you what the end of the month is. This solves the mystery as to whether or not the month ends on the 31st or the 30th, or in February the 28th, or the 29th if you're in a leap year. EOMONTH, end of month. 

EDATE. This will tell you what the date will be several months from the start date. So if you enter the start date, and then you enter a couple of months, you'll figure out what the date is. 

I'm going to do five quick examples, and we'll take a look at the exercise that students in the class would normally be given to complete. 

DAYS Function 

I want to figure out the total days between 531 and 324 2020. So I'll type in equal "days." I'll press tab. That puts in the open parentheses. 

Now I have a choice. I have to select the date. It's either gonna be the end date or the start date, but I don't have to guess because if I take a look at the arguments right after I put in the open parentheses after days, I can see end date comes first. 

So I'll select that by using the left arrow key. Then I'll enter a comma, and then I'll choose the start date. I'll press tab. There are 663 days between those two dates. 

NETWORKDAYS Function

Now I want to figure out the total number of workdays separating those two dates. 

That's going to be equal to "net," and that's the minimum I need to type. I can now press tab. If I press tab, Excel automatically completes the name of the function and puts in the open parentheses. But this time if I pay attention, I'm not selecting the end date first I'm selecting the start date first. Then I'll enter a comma and select the end date. 

I do have an optional parameter and that is holidays. I can use that when I have holidays to include in the NETWORKDAYS function. I'm going to press tab. 

WEEKNUM Function 

I want to figure out what the current week is for 324 2020. So I'll type in equal, and then "weekn," press tab, and then I'll select the serial number, which is another way of referring to the date. And then all I have to do is press enter and out of 1 to 52 weeks, it'll tell me what the current week is. We're in the 13th week of the year. All right.

EO Function 

I'm going to move down to Future Date. I want to find out the end of the month three months from now. So equal, "eo," as in end of month, I'll press tab. I'll select the start date, enter a comma and select a number of months. Three. Three months from now, the end of the month will be 6/30/2020. 

EDATE Function

I say to someone, let's have lunch six months from now. So I want to figure out what that date is. So "e," and I can press tab, then select the start date, enter a comma, select a number of months. I'll press enter. That is 9/24/2020. 

Exercises

Now let me move down. This is the exercise students would complete in class. I'm going to briefly go through it here. Total days. We'll go back to days. Again, end date comes first, then we'll enter a comma, and I will select the start date. 

Next, total workdays. Equal, "net," tab, I'm going to select the start date, enter a comma, and then select the end date. I'll press tab. Why tab? Because that's going to take me to the next cell I need to be in to type the next function. 

All right. Equal, "net," tab, start date, comma, end date. But this time I'm going to exclude some holidays. I have a holiday schedule over on the right. I'll navigate over to that holiday schedule and use the shift key to select multiple dates. I don't have to select them individually. I can select them as a group. And so that is going to exclude those 10 holiday dates. 

So if I press tab right now, you'll see that the number of days has been reduced by 10, because I'm selecting 10 days to remove from within those two dates. 

Week number. I'm going to choose the start date. Why? Because we already know that we are in the 13th week. I want to see the other possible weeks using the dates for start date. I'll press enter. And there you go. 

Now, when I copy this formula down for the rest of the dates, I'll press Control D, and I have green triangles. Green triangles usually mean trouble. I need to check here to see what's going on. I'll press F2. Shouldn't take me too long to figure out what is wrong with this picture, and what's wrong with this picture is I'm not selecting all the holidays. It appears this is another one of those relative reference issues. 

The holiday schedule needs to be the same, so I need to lock that range. It shouldn't be moving. The start and end dates should be moving as I move down the row, but not the holiday schedule. So I'm going to go back to the very first formula, and I'm going to lock the holidays. I'll press F4. I'll press enter. Now use autofill, and that should clear up the problem and I should not see any more green triangles. If I go into a cell and press F2, I am confirming that that has solved the problem, and I now have the correct number of work days without holidays. 

Recap

So in this section, we took a look at how to use the DAYS function, NETWORKDAYS, WEEKNUM, EO, end of month, and EDATE functions.