Calculate Future Date

ggeorge

I need to calculate a due date that is 18 months out. I have a column for "Actual Run Date" and I have a column for "Expiry Date" so the "Expiry Date" column needs to automatically calculate an 18 months expiry date once I enter the date on the "Actual Run Date".

For example: if the "Actual Run Date" happened on 07/13/17 then the "Expiry Date" column should automatically populate the date 01/13/2019

Any help will be much appreciated

Thanks!

Mike Wilday

Do you need only the month number to change? Can you use #of days to calculate a near target date? 

=[Actual Run Date]23 + 547.5 

Putting that in the Expiry date column should get you close to 18 months... if you want to calculate the exact month you could deconstruct the date. 

Otherwise, the calculation could get pretty tricky. 

ggeorge

Thanks Mike!

I need the actual date, month and the year based on the run date. I did put your formula, and it was off by one day

Chris McKay

Hi ggeorge,

You're going to run into issues with exact dates:

1. What is the date 18 months away from August 29th 2016? It's not February 29th 2018 as that date doesn't exist.
2. Neither does June 31st 2018, so a December 31st 2016 date will also cause problems.
3. And let's not even get started on leap years

The best you can hope for is an approximation based on the next business day, which handily Smartsheet does by default when you calculate an invalid date (as per the above). With that in mind, I'd use the following formula:

=DATE(IF(MONTH(Date1) < 7, YEAR(Date1) + 1, YEAR(Date1) + 2), IF(MONTH(Date1) < 7, MONTH(Date1) + 6, MONTH(Date1) - 6), DAY(Date1))

Mike Wilday

Chris, nice calculation! I was thinking something like that was possible but didn't have time to put something like that together. I am bookmarking this one for future use!

Chris McKay

Thanks Mike