Excel Tip - Instantly calculate future dates

You may often need to calculate future dates in Excel based on dates that you already have.  For example, you may need to know what an invoice due date will be in 30 days but not including weekends.  Find out how, by using the WORKDAY.INTL function...

  • Open a sheet that contains data with a date, or type the example above on a new sheet

 

  • In the cell you want the future date to be displayed, type =WORKDAY.INTL and the Function autocomplete will provide a list of functions.  Select WORKDAY.INTL…

 

  • Click on the Start date you wish to add days to and the cell reference will be inserted into the formulae:

 

  • Type a comma and then the number of days to be added, for example, 30 for 30 days:

 

  • Type a comma, then which days of the week you consider to be non-working days will be displayed, type 1 for Saturday and Sunday or one of the other numbers to exclude different days of the week:

 

  • Type a comma, if you have any bank holidays or company holidays to include in your non-working day date calculation, type them on the spreadsheet and select them in this last part of the formulae. You will need to make this range absolute by pressing F4 to fix the cells as we will be copying the formulae down column C:

 

  • Select the range of the holidays in the formulae and press F4 to fix the range.  Dollars will appear on the range cells.  Press RETURN to finish the formulae:

 

  • Fill the formulae down the column by Double-Clicking on the Auto Fill handle in the bottom right corner of the formulae cell:

 

  • The formulae will be filled to the bottom of the data and will count 30 days, but exclude weekends and holidays stated in the holiday range:

 

 

Using the intl function you can easily calculate start and end dates for projects or review dates or staff holiday periods.

Pin it