Duration Formula When Missing Values

[Lauren_Prete]

Hi brains trust,

Is someone able to assist with creating a duration formula that returns a “0:00” result when missing inputs?

I need to track team processing time on a particular project and currently have the following formula inputted under column “DURATION” which works a treat if all columns are correctly filled in:
{TIME PROJECT FINISHED}-{TIME PROJECT STARTED}

I would like the “DURATION” column to return “0:00” when there is value in “TIME PROJECT FINISHED” but not “TIME PROJECT STARTED”, and vise-versa. This is so I can chase whoever is inputting the record to ensure they are doing so in full and correctly.

I’m thinking this will be an IF/AND statement but I’ve only just got the hang of the nested IF statements so this is really pushing my working knowledge!

Thank you in advance for your formulaic wizardry!

[kuovonne]

It’s a little hard to write the formula without knowing the field types of the input fields, but this should work.

IF(
  AND( {TIME PROJECT FINISHED} & "", {TIME PROJECT STARTED} & "" ),
  {TIME PROJECT FINISHED}-{TIME PROJECT STARTED},
  0
)

[Lauren_Prete]

Ah! @kuovonne that worked perfectly!

Thank you so much!