This week, Alex is going to help a client with some financial reports and models. His first task is to improve the customer invoices report, to include some needed date information. For this, he will need to use some of Excel's more specialized date functions. The first thing that Alex is going to calculate is when the invoice is due. Invoices are due ten working days after the end of the month from which they are sent. So Alex has to create a calculation that will determine when each of these invoices are due. To do that, he has to work out how to get the end of the month, and then we can think about how to add on the ten working days. Excel has a useful function that can help us with this called End of Month. So start by typing =eo, select =EOMONTH. And then the first argument it needs is the start date, which is our invoice date, and then a comma. Now the months argument gives us quite a few options. If we want the end of the month for the date we've selected, we just type a 0. If we wanted the end of the month for the previous month, we could type a -1. And if we wanted the end of the month of this quarter, so three month's time, then we could use a 2. Seems a bit weird, but the counting starts from 0. But in this case, we want the end of this month. So we're going to type 0 and Enter, and it worked beautifully. It will also work very well for leap years. So if I had the date the 2nd of the 2nd, 2016, you see that gives us the 29th, so no worries with leap years. So that's half the problem solved. So now we need to add on our ten working days. If it was just ten elapsed days, we could do plus ten, but we want to factor in weekends and potentially holidays as well. To do this, we have two options. The Workday function and the Workday International function. They're very similar, and let's have a quick look at both of them. So if I click in here and type wor and Tab to select WORKDAY, you'll see the WORKDAY function expects three arguments. The start date, which we've calculated, the number of days we want to add, and you can even specify any holidays. Workday international is a newer function. It was introduced in 2010 and it gives us an added bonus. So I'm going to select that one this time and you'll see work day international is very similar. But it also allows us to specify when our weekends are. WORKDAY assumes it's Saturday and Sunday, but it's quite possible that in different regions or just different organizations, your weekends work very differently. So when I type a comma here I'm going to put my ten working days, and then a comma, and you'll see we get a whole lot of options we can choose from. If you go for the first one, you'll get Saturday, Sunday, but there are many other options as well. If, however, you have a unique situation, like maybe you have no weekends or you take off Wednesdays and Sundays, then this function allows you another option and that's to specify a mask. So for example, if I open my quotes and I type 000, that means Saturday, Sunday, Monday are all working days. But Tuesday, and Wednesday, and Thursday are weekend days, and Friday is back to being a work day. You can specify your own combination. We're going to go for the simple option here, but it's nice to know you have the mask option. So I'm just going to click Saturday and Sunday, and then type a comma. And then, I'm going to select my holiday dates. And it's a good idea to have them in your workbook. And don't forget to press F4 to lock those in place. I'm going to close my records, and press Enter. And we've calculated our due date, ten working days into the next month. And I can now double-click to copy that down. The other thing we want to do is send out reminders two months after the invoice date. So we need to be able to calculate our reminder date. We want to be able to, basically, add two to the month, and the function that will allow us to do that is the End Date function. We type =ed, and Tab to select =EDATE. And then we click on our start date again. And we can specify the number of months to go forwards or backwards. In this case, we want to go forwards two months so we're going to type 2. And that's all there is to it. But if we'd wanted to add on a year, for example, we could add on 12. So this is a very useful function in our financial models as well, and we'll see that later on. I'm going to press Enter. There's our date exactly two months into the future, and we can double-click to copy that down. So there are a few really useful date functions that are not so well known, but can really help enrich your models and your reports. Next up, we're going to look at some of the financial functions that you might find useful. [SOUND]
