Excel 2007 gives you many tools to calculate the number of calendar days or the number of working days between two dates. When you calculate the number of working days, Excel excludes weekends automatically. But you also need to maintain a list of business holidays so the functions can leave them out as well. I want to stress that it is absolutely vital that you store the dates in cells with the Time/Date format and not as text. I have done that in this worksheet, which I'm using as my example. I have an Initiation Date and a Maturity Date for an investment and you'll notice that these cells both have the date format that is absolutely vital.
If you want to determine the total number of days between two dates, all you need to do is subtract one date from the other. So in this case, we have the later date of December 24th that's in cell B5, when we subtract the initiation date which is in cell B3 and we find that we have 174 days, but that includes weekends and holidays. How about if you want to determine the number of working days between those two dates? For that, you use the NETWORKDAYS function. So you type in =NET and then you have the start date which is the earlier date and that is in cell B3, and end date which is in cell B5 and then a list of holidays.
Now, I can't switch over to the worksheet right now because I'm in the middle of entering a formula but the holidays are on the Holidays worksheet in a table called Holidays2010. So what I'll do is I'll just start typing in the name of the table, holidays, and there you see Holidays2010, and they are in the Holiday column. So I have completed the formula, hit Return and you'll see that you have 119 working days between July 3rd and December 24th of 2010. Now, let's say that you want to determine the next workday after December 24th of 2010.
The next calendar day is December 25th and that's Christmas, a holiday in the United States. So that will not be a working day. To find the next working day after a given number of working days, excluding weekends and dates identified as holidays, you use the WORKDAY function. Now, when I enter the formula as you would expect, workday, start date in cell B3, the number of days which is in cell B9 and then the list of holidays which are in the Holidays2010 table in the Holidays column.
You get an incorrect result. Instead of getting the workday after December 24th, you get the day prior to that. So what happened? Let's take a look at the formula and it appears to be right, but what's happening is that Excel is counting the initiation date, July 3rd, as the first day. So what you need to do is add one to the working days total. So that value comes from cell B9 in this case. So you type +1, hit Return and you get the next working day which is December 27th, the Monday after Christmas.
Finally, some financial calculations require you to use a 360-day calendar. That assumes a 30-day month and 12 months a year. You'll use a 360-day calendar for some bond transactions and also for loans between banks. To calculate the number of days between two dates using a 360-day calendar, you use the DAYS360 function. So to determine the number of days between our initiation date and our maturity date, using a 360-day calendar, we use the start date, E3, the end date of B5 and the method we leave blank because we'll be using the default which is 360, 30 days, 12 months and Return.
And we'll find that using that system, we have 171 days between our initiation date and our maturity date. Working with dates in Excel does seem confusing at first, but most of the time, you can use the techniques in this lesson to determine the number of days you have to complete the task or for an investment to mature.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70309 Viewers
80 Video lessons · 127556 Viewers
52 Video lessons · 62474 Viewers
59 Video lessons · 48183 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Your file was successfully uploaded.