How do I write a formula for a projected end date?
This is for trainees and I need to determine when the training will be completed. I have the start date and length of training in two separate columns. For example:
What formula do I need for the projected end date column?
Answers
-
Hi, @KRogers9! This calculation is easiest if the Orientation Length is in days, not months, since you could just use the formula [Start Date]@row+[Orientation Length]@row. You also have to make sure your 'End Date' is formatted as a Date field.
The link at the bottom of my post shows you how to use the DATE() function to try to add the # months, but when I tested it, I ran into trouble when the date went into the next year, as it returned INVALID. I think your best bet is to enter duration as days, (or calculate days as months x 30) and use the formula above. Either way, do not include the units in the cell, but note them in the column header. Here is my test page to see what happened when I tried the various calculations:
Hope this helps! @Stacey C
https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates
-
This is very helpful!! Thank you!!
A secondary question I have, is it possible to make a metric sheet with dates? I was using these start dates to create a metric sheet in order to build a stacked bar graph for a dashboard. Anytime I change the formatting of the cells to DATE, they won't link to the metric sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!