Help

Trouble with DATETIME_DIFF when nested within an IF formula

Topic Labels: Formulas
Solved
Jump to Solution
945 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Alex_Jones1
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey everyone,

Hoping somebody can help here.
I’m having trouble returning some info from a formula I have created.

I have a table to show the duration of certain tickets that are received for problems. I have a Date Raised field, Date Resolved field, and a Days Outstanding field.
I want the Days Outstanding to return the difference between Date Raised and Date Resolved when there is a value in the Date Resolved field, and I want it return the difference between Date Raised and TODAY() when the Date Resolved field is empty.

I’m currently using this formula:
IF({Date Resolved}!=BLANK(), DATETIME_DIFF({Date Resolved},{Date Raised}, ‘days’), DATETIME_DIFF(TODAY(),{Date Raised}, ‘days’))

Screenshot 2021-02-19 at 15.49.27

It’s working fine when Date Resolved is populated, but returns NaN when it’s empty.

Screenshot 2021-02-19 at 15.49.42

I have created another field just to check that the today-date resolved formula works and it’s returning exactly what I want, it just doesn’t work when it’s inside the IF formula

can anybody explain this and show me a solution?

Thanks in advance!

1 Solution

Accepted Solutions
augmented
10 - Mercury
10 - Mercury

Hey Alex, try taking out the !=BLANK() and just say IF({Date Resolved}, …

See Solution in Thread

3 Replies 3
augmented
10 - Mercury
10 - Mercury

Hey Alex, try taking out the !=BLANK() and just say IF({Date Resolved}, …

Worked! Thanks my friend!

As a side note, this could also be simplified a little by putting the IF() function inside a single DATETIME_DIFF():

DATETIME_DIFF(IF({Date Resolved}, {Date Resolved}, TODAY()), {Date Raised}, 'days')