Calculating days formula that ends in a working day
Hello community,
As the title explains, I am looking for a formula that adds 30 days to a specific date. However, sometimes these dates are during the weekend. I want a formula that would be able to add 30 days to a date. If that answered date is on a Saturday or Sunday, it will go to the closest working date. Please let me know if you need any more info. Thank you!
Best Answer
-
@Adam1234 , sorry , that should just be your date column aswell. Missed out editing that reference after I tested it quickly in my sheet
I'm passionate about helping you leverage the truly awesome power of smartsheet!
Answers
-
=WORKDAY([date]@row,30)
That should do it for you
I'm passionate about helping you leverage the truly awesome power of smartsheet!
-
Hi @SmartLew ,
Thank you for your response. Not quite what I was looking for. I believe this formula gives me an additional 30 working days. I want the formula to show that 30 regular days have passed, and if that answer lands on a Saturday or Sunday, it will give me the next working day. Please let me know if that makes sense. Thank you!
-
gotcha!
try this
=IF(WEEKDAY([Date]@row + 30) = 7, [Date]@row + 32, IF(WEEKDAY([Date]@row + 30) = 1, [Date]@row + 31, [Column8]@row + 30))
This adds 30 days, and then checks if it's a Saturday or Sunday (smartsheet identifies Sunday as Day 1 of the week). If so it adds the extra days to get the date to Monday. if it's not a Saturday or Sunday, it keeps it as 30 days added only.
Let me know if that works for you!
I'm passionate about helping you leverage the truly awesome power of smartsheet!
-
Hi @SmartLew ,
perhaps I am missing something, I'm not quite sure what the [Column 8] is referring to in the end of the formula.
-
@Adam1234 , sorry , that should just be your date column aswell. Missed out editing that reference after I tested it quickly in my sheet
I'm passionate about helping you leverage the truly awesome power of smartsheet!
-
I see where I messed up. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!