IBM Apptio

 View Only
  • 1.  calculate end date

    Posted Tue March 05, 2024 03:18 PM

    I need to calculate an end date for some assets. I have start date and useful life in months. Does Apptio have a function similar to EDate in excel?


    #CostingStandard(CT-Foundation)


  • 2.  RE: calculate end date

    Posted Wed March 06, 2024 03:24 AM

    I had such a painful experience trying to add dates together in Apptio but I did end up finding out how to do it. I didn't end up using it as I found a bit of a workaround.

    You can convert a date column into months, then add that month value of Start Date to the Useful Life value, but to then get that month value back into a date, you need to convert it to seconds and then you can use =DateFormat(). To keep it super simple I'd do it all in separate columns like this:

    Start Date in Months =Months(Start Date)

    End Date in Months =Start Date in Months+Useful Life

    End Date in Seconds =End Date in Months*30*24*60

    End Date =DateFormat(End Date in Seconds,"MM/dd/yyyy")

    The only drawback is that you have to multiply by days, then hours, then minutes to get to seconds so I just had to use 30 days for a month.

    Hopefully this has helped and at least gives you an idea of what you could do.

    If anyone can think of a nicer way to add months on to an existing date I'd love to hear it as it would still be useful for us to use as well.

    Thanks,

    Oli




  • 3.  RE: calculate end date

    Posted Fri March 15, 2024 01:55 AM

    American date format! Ahhhhh!

    Ok not tested at all this below code but think you should be able to do this with some fairly simple mathematics and the Months() function.

    Months Since AD = 1970 *12

    Start Date Months = Months(Start Date)

    End Date Total Months = Months Since AD + Start Date Months + Useful Life

    End Date Year = Trunc(End Date Total Months / 12)

    End Date Month = Mod(End Date Total Months / 12) + 1

    End Date Day = Trim(Split(Start Date,2,"/"))

    End Date = End Date Month & "/" & End Date Day & "/" & End Date Year



    ------------------------------
    Mark Johnson
    Delivery Manager - TBM Office
    Origin Energy
    +61 467 863 134
    mark.johnson1@origin.com.au
    ------------------------------



  • 4.  RE: calculate end date

    Posted Thu March 21, 2024 03:51 PM

    thanks for your help. This totally worked!




  • 5.  RE: calculate end date

    Posted Tue March 12, 2024 05:11 PM

    You could use the Days function to convert the start date, add the Useful Life (also converted with Days). Then use DateFormat on the End result - it's not much better than Oliver's solution but the numbers are not so large.

     Days function link




  • 6.  RE: calculate end date

    Posted Wed March 13, 2024 05:13 PM

    i don't think that date format function works like i was hoping it would. I need something that can do the Days function but in reverse




  • 7.  RE: calculate end date

    Posted Thu March 14, 2024 10:12 AM

    We are all trapped in the grip of 1970s programming ...

    Oliver is right - anything involving dates is doing calculations of the number of seconds since 1970. I ran through all of the levels as separate calculation steps in the image below. If I were implementing this as an actual table transform, I would condense the Useful Life calculations into one step (life/12*365*24*60*60). This doesn't help you with leap years but I guess you could use 365.25 which would get you a little closer to the actual number.




  • 8.  RE: calculate end date

    Posted Fri March 22, 2024 05:03 AM

    On a slightly humorous note, by 2070 none of this might work (or will it?) On the flip side, I should be well over 100 by then 😈😈😈



    ------------------------------
    Regards, Guillermo
    ------------------------------



  • 9.  RE: calculate end date

    Posted 26 days ago

    And don't forget the "Year 2038 problem." 🙊



    ------------------------------
    Jenny Franklin
    ------------------------------