Course Delivery Date formula
I have a formula that seems simple, but for some reason it's calculating an extra day. There are three columns and the first is duration in days, the next is delivery date, then the third is the end date. What should be happening is the delivery date should be counted as one day, then the calculations should be added to that.
So in the first row the duration in days is 1, the estimated delivery date is 6/28/2024, so the course end date should be 6/28/2024 which is one day. (it's adding the extra day instead of counting the start date as 1 day.
The next rows are doing the same thing, the bottom row shows a duration of 2 days, so the Estimated Delivery Date is 10/29/2024, and the estimated end date should be 10/30/2024 and not 10/31/2024.
The formula used =[Estimated Course Delivery Date]@row + [Duration (in days)]@row
I've tried a few things but none seem to be working.
Also - Is there a way to have it ignore weekends and only use work days?
Best Answer
-
Thanks Paul, it didn't include the work days question, but it got me on the track. I ended up using
=WORKDAY([Estimated Course Delivery Date]@row, [Duration (in days)]@row - 1)
That seemed to calculate it out correctly.
Answers
-
You would just subtract 1 from the final output.
=[Estimated Course Delivery Date]@row + [Duration (in days)]@row - 1
You can use the NETWORKDAYS function to account for only workdays.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul, it didn't include the work days question, but it got me on the track. I ended up using
=WORKDAY([Estimated Course Delivery Date]@row, [Duration (in days)]@row - 1)
That seemed to calculate it out correctly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!