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
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!