How to normalize the dates and subtract them from each other to get elapsed time?

[cotyp]

Hello,

I am trying to normalize the dates on the below fields and subtract them from each other. How would I go about doing that? Is there a way that if there is no termination date, show something that says, Active and employed for X amount of time?

Thanks in advanced!

[493669]

Try like this:

|eval Terminates Date=if(isnull('Terminate Date'),strftime(now(),"%m/%d/%Y"),'Terminate Date') |eval Days=if(isnotnull('Terminate Date'),round((strptime('Terminate Date',"%m/%d/%Y"))-(strptime('Start Date',"%Y-%m-%d")))/86400,"Active and employeed for ".(round((strptime('Terminates Date',"%m/%d/%Y"))-(strptime('Start Date',"%Y-%m-%d")))/86400)." Days")

[cotyp]

nice! How would I make sure both the Terminate and Start date are shown in the same format? For instance XX-XX-XXXX?

[493669]

like this i.e. first converting time into epoch and then set format using strftime

...|eval Start Date=strftime(strptime('Start Date',"%Y-%m-%d"),"%m-%d-%Y"),
Terminate Date=strftime(strptime('Terminate Date',"%m/%d/%Y"),"%m-%d-%Y")

[493669]

please accept the answer if it resolves your query.