In last week’s tip we explained how invoice due dates could quickly be calculated by using the WORKDAY function. But, suppose your weekend falls on Friday and Saturday, then how do you calculate the invoice due date? In that case you will have to use the WORKDAY.INTL function because the WORKDAY function uses standard weekend days.
WORKDAY.INTL returns a serial number of the date, before or after a specified number of workdays with custom weekend days. Just ensure that you convert the number to a date format of your choice. As you enter the formula a list of the custom weekend days will be displayed. This gives you a chance to select the custom weekend days of your choice.
Note: Download the sample workbook to practice this exercise
Applies To: Microsoft® Excel® 2007, 2010 and 2013
1. With reference to the sample workbook; select cell E2.
2. Type =WORK
3. Double click on WORKDAY.INTL from the list that will be displayed.
4. Enter the start date and days. From the custom weekend days list select number 7, where the custom weekend days falls on Friday and Saturday.
5. Press Enter and copy the formula to cell E8.
The invoice due dates will thus be calculated with custom weekend days. This function can also be used to calculate the date when a contract will expire; you replace due days with the duration of the contract and calculate the contract expiry date.
