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

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

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

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