Sometimes it’s useful to consider only working days (excluding weekends and holidays) when doing date calculations in Google Sheets, e.g. calculating a date x working days ahead. Google provides the function WORKDAY to help take some of the pain out of these calculations. The syntax is:
WORKDAY(start_date, num_days, [holidays])
where start_date is the date from which counting is to begin, num_days is the number of working days to count, and the optional holidays parameter is a range or an array containing dates to be excluded, i.e. not to be considered as working days. (The function automatically excludes Saturdays and Sundays, so if the dates included in holidays include a Saturday or Sunday, it will have no effect on the result).
The function returns an end date based on counting forward from the start date by the specified number of working days; or you can use a negative value to count backwards. (NOTE: some of the Google documentation incorrectly states that the function returns the number of days, rather than a date)
Here’s the function in action, without the use of the holidays parameter:
To specify some holidays, let’s put some dates in column E, and alter the formula to include this range in the calculation:
As you can see from the figure, the resulting end dates have now moved forward, where relevant, to account for the two extra days of holiday.
If you work in a society that considers a weekend to be something other than Saturday and Sunday, there’s the function WORKDAY.INTL, which lets you specify which days are weekend days:
WORKDAY.INTL(start_date, num_days, [weekend], [holidays])
Here, the optional weekend parameter is a number or string representing which days of the week are considered weekends. The weekend parameter can be specified in one of two ways:
The String method, where weekends can be specified using seven 0 and 1 characters; the first number in the string represents Monday and the last number, the following Sunday. A zero represents a work day, a 1 that the day is a weekend. For example, “0000111” would indicate that Friday, Saturday and Sunday are weekends.
The Number method, where a single number can be used as follows: 1 = Saturday/Sunday are weekends, 2 = Sunday/Monday, and so on until 7 = Friday/Saturday. Alternatively, 11 = Sunday is the only weekend, 12 = Monday is the only weekend, and so on until 17 = Saturday is the only weekend.
There are also functions to return the number of working days between two dates, which I cover in another post.