Team,

We have two columns as "Start Date" which is "mm-dd-yyyy" format and "No.of Months" in "INT" data type. We need to calculate "End date" for the same. In MS-EXCEL we have a in-built function called =EDATE(A2,B2) which gives you the end date in date format as below.  Can some one advise how to achieve this in apptio?

End Date = If(Mod({# Months} + Value(DateFormat(Start Date, "M")), 12) = 0, 12, Trunc(Mod({# Months} + Value(DateFormat(Start Date, "M")), 12))) & "/" & Split(Start Date, 2, "/") & "/" & Value(Split(Start Date, 3, "/")) + Trunc(({# Months} + Value(DateFormat(Start Date, "M")) - 1) / 12)

Dave,

Please be informed that the formula is not working correctly for the below data set, hope we need to modify the formula, could you please help n the same?

Perhaps the timestamp (0:00) is the issue.

One option to remove it:

Acq Date Modified = Split(Acquisition Date, 1, " ")

Chris/Team,

Thanks much for the response. Per the latest advice from you, I have modified the date format to mm/dd/yyyy, the formula was working fine except for the below data sets, due to number of days (30/31), also leap year is another criteria to look out. Could you please advice on the below?

Formula: End Date = If(Mod({# Months} + Value(DateFormat(Start Date, "M")), 12) = 0, 12, Trunc(Mod({# Months} + Value(DateFormat(Start Date, "M")), 12))) & "/" & Split(Start Date, 2, "/") & "/" & Value(Split(Start Date, 3, "/")) + Trunc(({# Months} + Value(DateFormat(Start Date, "M")) - 1) / 12)

Chris/Team, I'm trying to use this formula to find out the End date and as mentioned above we are getting invalid dates for the above scenarios. I was trying to use the Durationofmonth() function to get the last day(30 or 31 or 28 or 29) for the Invalid date.

Below are the formulas i'm using

End Date column returns 09/31/2011 using the above formula.

End Month = Value(Split(End Date,1, "/")  - Defined as Numeric

End Year = Value(Split(End Date,3, "/") - Defined as Numeric

Last Day of Month = DurationOfMonth("d",End Month,End Year)

DurationofMonth function is not working as expected when column names or formula is used.  This function is returning blank value. 

However in general, DurationofMonth("D",9,2011) returns correct value.

Could you please let us what the issue is while passing columns names in the function?