Datetimediff null values

[jmathew94]

Hello,

I am trying to find the difference between two timestamps using the Datetimediff function. I used substring to remove the AM/PM and EST from the timestamp however it is still not pulling in. I believe my issue is around the data type or formula for datetimediff? Please help

[ShankerV]

Hi @jmathew94 

The date is not in the ISO format.

Please convert to YYYY-MM-DD format with the help of date time tool or using the same formula tool also.

[binuacs]

@jmathew94  you need to use the DateTimeParse function

Datetimediff(DateTimeParse([Approving Time Formula],’%d-%b-%Y),DateTimeParse([Input Time Formula],’%d-%b-%Y),’hours’)

[ShankerV · Accepted solution]

Hi @jmathew94 

A sample use case for your scenario.

Step 1: Input

Step 2: 

dd-Mon-yyyy hh:mm:ss

Step 3: 

Step 4:

DateTimeDiff([AT1],[IT1],"hours")

Many thanks

Shanker V

[jmathew94]

Great Thanks @ShankerV! This solution worked. One last question- if I am wanting to add minutes to the hours of completion how would i do so? For example, -1.23 hours or 5.32 hours

[RobertOdera]

Hi, @jmathew94,@ShankerV , @binuacs 

Actually, it is a little more tricky than before answered.

Consider leveraging %d-Mon-%Y %I:%M:%S %p because AM/PM has implications in time duration

Check out the below and mark it as an acceptable solution if it works for you. Cheers!

[ShankerV]

Hi @jmathew94 

To Achieve the result in minutes also like 1.54 etc.

Tweak the formula in the tool.

(DateTimeDiff([AT1],[IT1],"minutes"))/60

Many thanks

Shanker V

[jmathew94]

Thanks this works however I am having trouble figuring out how to round this to the two decimals. Looks like some come through fine and some don't. 

[ShankerV]

Hi @jmathew94 

The below change in the datatype will help to limit with 2 decimal places.

Many thanks

Shanker V