Excel: Date Functions for Calculation

Date functions. In this section, we'll take a look at five Excel date functions you can use to calculate dates in the future, or the total number of days between any two dates, as well as figure out the week number for the current date that you're in. So, let's start with the first of five date functions, the days function.

The days function will tell you the amount of days between any two dates, the end date and the start date. Network days, this will tell you the net work days between any two dates, basically days that do not include Saturday and Sunday. So, hence network days, since you don't work on a 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 in 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. EO month, end of month. EDate, this will tell you what the date will be several months from the start date.

So, if you just enter in 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. So, I want to figure out the total days between 531 and 324, 2020.

So, I'll type in equal, D-A-Y-S, I'll press TAB, that puts in the open parentheses, and now I have a choice. I have to select the date. It's either going to 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.

Now, I want to figure out the total amount of workdays separating those two dates. So, that's going to be equal to N-E-T, 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, 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 network days function. I'm going to press TAB. I want to figure out what the current week is for 3-24-2020.

So, I'll type in double equal and then W-E-E-K-N, 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. So, 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 E-O, as in end of month. I'll press TAB. I'll select the start date, enter a comma, and select the number of months, three.

Three months from now, the end of the month will be 6-30-2020. Future date, 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 the number of months. I'll press ENTER. That is 9-24-2020.

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 we'll select the start date.

Next, total work days, equal N-E-T, 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 N-E-T, 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 we go. Now, I want to 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.

So, in this section, we took a look at how to use the days function, network days, weeknum, eo, end of month, and edate functions.