Skip to main content

Attempting to use a table to calculate the total time spent on the project. The Formula was working but returning NaN for certain cells that had no stop time. Adding in the following statement / IF({STOP TIME}="","", / seemed to fix the Issue of NaN being returned however the output data is only being returned in integers now and will not allow me to format the function to display time (( 00:00 )) below is my formula what changes need to be made to allow for proper duration display?


IF({STOP TIME}="","", DATETIME_DIFF({STOP TIME},{START TIME}))

Try rewriting your formula like this:


IF({STOP TIME}, DATETIME_DIFF({STOP TIME},{START TIME}), BLANK())

The reason is that when you tell the cell to return “”, you are asking for a string instead of a duration (an empty one, yes, but a string none-the-less). This changes the data type for the column. But if you ask it to return BLANK(), the data type for the field should remain defined as a duration, as your positive value dictates.

Try rewriting your formula like this:


IF({STOP TIME}, DATETIME_DIFF({STOP TIME},{START TIME}), BLANK())

The reason is that when you tell the cell to return “”, you are asking for a string instead of a duration (an empty one, yes, but a string none-the-less). This changes the data type for the column. But if you ask it to return BLANK(), the data type for the field should remain defined as a duration, as your positive value dictates.



Thank you Jeremy, that fixed things easily. it didn’t occur to me that I had asked for a string since it was blank. Your help was appreciated.

Reply


Terms & Conditions