Date Calculation Formula excluding weekends and bank holidays

Marcy Morris

Hello - I need some guidance. I used this formula https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates?_ga=2.214560144.666552865.1680531310-742068913.1676652705 to calculate weekly, bi-weekly, monthly, bi-monthly, quarterly, bi-quarterly, annually, bi-annually dates and then I realised I need to exclude weekends and ideally bank holidays.

The weekly and bi-weekly were easy but now I'm stumped🤪 on the monthly, bi-monthly, quarterly, annual, bi-annually. I know simply adding on an extra 30, 60, 90 days isn't the Smart way to execute the task. Some guidance (a solution😏) would be appreciated. Here's an example of the fields I'm using

=[Confirmed Comms Send Date]@row + 14 and I added WORKDAY to the start of the formula and it worked for the 7 and 14 days but not for the monthly etc., =WORKDAY([Confirmed Comms Send Date]@row, +7)

Paul Newcome

You should be able to use the same WORKDAY syntax that you used in the other 2 then:

=WORKDAY([Confirmed Comms Send Date]@row, +30)

Paul Newcome

That simply adds one month to the date. It does not adjust anything for working days or holidays.

Paul Newcome

Are you able to provide the formulas you actually used?

Marcy Morris

Hi @Paul Newcome

Thanks for picking this up. Here's the formulas I have used for:

Weekly =WORKDAY([Confirmed Comms Send Date]@row, +7) - it works

Bi-weekly =WORKDAY([Confirmed Comms Send Date]@row, +14) - it works

The struggle is real for the Monthly, Bi-Monthly, Quarterly, Annual, Bi-Annually

This is what I tried for MONTH. The formula returns a date but not for the next month.

=WORKDAY([Confirmed Comms Send Date]@row, MONTH([Confirmed Comms Send Date]@row, +30))

For example, the Confirmed Comms Send Date is 27 March the date returned with the formula is 30 March

Paul Newcome

You mentioned in your first post that you already have formula for those but are struggling to get them over into excluding weekends and holidays? What are the existing formulas that work but do not exclude the weekends and holidays?

Marcy Morris

@Paul Newcome here's the formula that I originally used that includes weekends and holidays

=DATEONLY([Confirmed Comms Send Date]@row + 30)

Paul Newcome

You should be able to use the same WORKDAY syntax that you used in the other 2 then:

=WORKDAY([Confirmed Comms Send Date]@row, +30)

Marcy Morris

@Paul Newcome o.k so it is literally a case of counting 30 or 31 days for monthly, 60 days for bi-monthly and 365 for annual using the WORKDAY syntax?

What does this formula do? =DATE(YEAR(date12), MONTH(date12) + 1, DAY(date12)) I was under the impression/thought it would be possible to calculate using a formula based on the below instead of using numbers to calculate the number of days. Not sure if I'm explaining it correctly.

Paul Newcome

That simply adds one month to the date. It does not adjust anything for working days or holidays.