Date Calculation Formula excluding weekends and bank holidays
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)
Best Answers
-
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)
-
That simply adds one month to the date. It does not adjust anything for working days or holidays.
Answers
-
Are you able to provide the formulas you actually used?
-
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
-
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?
-
@Paul Newcome here's the formula that I originally used that includes weekends and holidays
=DATEONLY([Confirmed Comms Send Date]@row + 30)
-
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 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.
-
That simply adds one month to the date. It does not adjust anything for working days or holidays.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!