Future Date Calculation

Shannon Craig

Hello,

I am trying to find a formula that will calculate out 90 days from a given date AND give the result as the last day of the month.  

The given date is Completion Date Estimate i.e. 10/31/2017 of a project and I would like a cost cut off date that is 90 days later on the last day of the month i.e. 01/31/2018.

One formula I have is =[Completion Date Estimate]ROW# + 90. This only gives me a count of 90 days later.  i.e 10/31/2017 to 01/29/2018

The next formula I have is =IF((MONTH([Completion Date Estimate]ROW#) + 3) < 1, DATE(YEAR([Completion Date Estimate]ROW#) - 1, MONTH([Completion Date Estimate]ROW#) + 3 + 12, DAY([Completion Date Estimate]ROW#)), DATE(YEAR([Completion Date Estimate]ROW#), MONTH([Completion Date Estimate]ROW#) + 3, DAY([Completion Date Estimate]ROW#))).  This formula works great, except if the completion date falls in Oct, Nov, or Dec.  Then the month count does not know to go from 12 to 1 and an error of #INVALID VALUE is given. 

Does anyone have any suggestions? 

Taylor F

Hello Shannon,

Thanks for posting. This was a fun one to create.

=DATE(YEAR(Date1 + 118), MONTH(Date1 + 118), 1) - 1

Essentially, this formula will produce the year and month from 118 days from your start date which will force it to be in the fourth month and not the third month (90 days). Then is creates a date that is the 1st of the fourth month. Then it takes the date and subtracts 1 day which will then be the last day of the third month.

The reason for this is because each month starts on the 1st but doesn't always go 31 days. If we created a larger formula that accounts for 28, 29, 30, or 31 days to be the last of certain months it would be a very large formula. This way we can find the first of the 4th month and subtract 1 day.

Let me know if you have any questions on this.