Hi Everyone,

I am struggling with dates in Apptio.

Is there any function in Apptio to add date, month with particular date?

For example :

“01/08/2016” + 15 Days = “16/08/2016”,

“01/08/2016”+ 8 months=”01/04/2017” (approx.)

Dates are in "DD/MM/YYYY" format

Thanks in advance

Faneshwar

For the first part, if the dates are instead MM/DD/YYYY in a Date column, one option is:

15 Days Later = DateFormat((Days(Date)+15)*60*60*24,"M/d/yy")

Days() seemed to require MM/DD/YYYY instead of DD/MM/YYYY, maybe just because of my Locale setting (in Edit Project Settings menu).

Thanks Christopher,

Can anyone help me with second part of the question please?

Thanks

Faneshwar

This works but can likely be simplified or improved upon:

8 Months Later = If(Value(DateFormat(Date Purchased,"M"))=4, 12,  Mod(Value(DateFormat(Date Purchased,"M"))+8,12)) & "/" & DateFormat(Date Purchased, "d") & "/" & Value(DateFormat(Date Purchased, "yyyy"))+If(DateFormat(Date Purchased,"M")>4, 1, 0)

You'll need to make sure that you increment the years as well.  I've got an example Excel file which breaks this down in a very basic fashion - inbox me at vjay@apptio.com and I'll send it to you.

The orange portion of the original formula increments the year when needed:

8 Months Later = If(Value(DateFormat(Date Purchased,"M"))=4, 12,  Mod(Value(DateFormat(Date Purchased,"M"))+8,12)) & "/" & DateFormat(Date Purchased, "d") & "/" & Value(DateFormat(Date Purchased, "yyyy"))+If(DateFormat(Date Purchased,"M")>4, 1, 0)

If you're seeing incorrect results, perhaps this is an issue related to the locale setting (as with the 15 Days Later function in the post above).

Check out my article Date Dimension Table. I think it might help.

This formula adds a month to a date. 

=DateFormat((Days(Service Month Date)+DurationOfMonth("d", Value(DateFormat(Service Month Date,"M"))+1, Value(DateFormat(Service Month Date,"Y"))))*60*60*24,"M/d/yyyy")

I think there's a slight error in that formula, Zakria.  I don't think you need the +1 in the month parameter of your DurationOfMonth function.  That would add the number of days in the following month, not the number of days in the current month.  I think the formula should be:

=DateFormat((Days(Service Month Date)+DurationOfMonth("d", Value(DateFormat(Service Month Date,"M")), Value(DateFormat(Service Month Date,"Y"))))*60*60*24,"M/d/yyyy")