How do I write a formula for a projected end date?

KRogers9

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?

Stacey C

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

KRogers9

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.