Help

Datetime difference calculated from the results of other two formulas

Topic Labels: Dates & Timezones Formulas
320 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Ed
4 - Data Explorer
4 - Data Explorer

Hey there.

I'm trying to get the duration of an activity by calculating the difference between 2 timestamps.

These timestamps are the results of a formula that shows the time as a box is checked at the start of the activity and another, at the end. 

The formula in Start Time and Finish Time fields are:

IF({Start}=1,DATETIME_FORMAT(SET_TIMEZONE((LAST_MODIFIED_TIME({Start})), 'America/Sao_Paulo'),'H:mm'),0)
IF(Finish=1,DATETIME_FORMAT(SET_TIMEZONE((LAST_MODIFIED_TIME(Finish)), 'America/Sao_Paulo'),'H:mm'),0)

Resulting this:

Ed_0-1690862128876.png

The formula in Duration field is: 

DATETIME_DIFF({Start Time},{Finish Time},'minutes')

 

And the result is "NaN" as seen above.

So the question is: how do I make it show time difference of the activity? 🤔

Any help is greatly appreciated.

Best vibes.

Ed.

1 Reply 1
Sho
11 - Venus
11 - Venus

Hi @Ed ,

The return value of DATETIME_FORMAT() is a string value.
Therefore, it is not treated as DATETIME and cannot be calculated.

How about this?

IF(AND({Start},{Finish}),
  DATETIME_DIFF(LAST_MODIFIED_TIME({Finish}),LAST_MODIFIED_TIME({Start}),"minutes")
)