1 Reply Latest reply: May 15, 2014 10:12 AM by Olly Bond RSS

    Subtract day/hr/min from Today() to return original date

    pth3x _

      I have a report with a column entitled "Age of Order."

      The values in the fields are formatted like this:

      6d 14h 26m

      I need a function that will subtract 6 days, 14 hrs and 26 min from Today() and so obtain the date and time the order was originated.

      Spent several hours on various strategies, all of which failed.

      Any hints?

       

      Thanks!

      patrick

        • Subtract day/hr/min from Today() to return original date
          Olly Bond

          Hello Patrick,

           

          (Mea culpa - Unix Time gives funny results)...

           

          Monarch lets you do this, just by subtracting days from dates.

           

          It's easiest if you split up your report input (assuming it's all on character field called ) with numeric calculated fields:

           

          Days=val(extract(ReportText;"";"d"))

          Hours=val(extract(ReportText;"d ";"h"))

          Minutes=val(extract(ReportText;"h ";"m"))

           

          Then, define a calculated field, numeric, called =Today()+Time().

           

          Now define a calculated field as numeric with 3 decimal places called = ((60Minutes)+(3600Hours)+(86400*Days))/86400

           

          Now define a date field called =[Now]-[Interval] - hopefully this should give you what you want.

           

          Best wishes,

           

          Olly