Duration Formula When Missing Values

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!

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
)
1 Like

Ah! @kuovonne that worked perfectly!

Thank you so much!

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.