Calculating invoice due dates in Microsoft® Excel® is easy. If you have struggled to calculate invoice due dates before, your worries are over – in this tip we’ll show you how to do this quickly using the WORKDAY function. The WORKDAY function returns a number that represents a date, before or after a specified number of work days. You can use WORKDAY to exclude weekend or holidays when you calculate invoice due dates or expected delivery times.
Note: Download the sample workbook to practice this exercise
Applies To: MS Excel 2007,2010 and 2013
1. With reference to the sample workbook; select cell E2.
2. Select the Formulas Tab then Date & Time
3. From the Date & Time function list ; select WORKDAY
4. Then enter C2 as the start date and D2 as Days.
- Start_date: A date that represents the start date. (required)
- Days : The number of working days before or after start_date. A positive value for days yields a future date; a negative value yields a past date. (required)
- Holidays . A list of one or more dates to exclude from the working calendar. The list can be a range of cells that contain the dates.(optional)
5. Select OK and copy the formula down to cell E8.
6. The value returned by WORKDAY function should be converted to a date as follows.
- Right click on the value
- Select Format Cells
- Select Date under categories
- Select the Date type
- Select OK
- The due date has been returned for each Invoice number
- The due date excludes weekends or holidays
