Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Formula with 0 value within IF statement

Topic Labels: Base design Data Formulas
Solved
Jump to Solution
1099 2
cancel
Showing results for 
Search instead for 
Did you mean: 
DanielP
6 - Interface Innovator
6 - Interface Innovator

I have this working formula to return 'Reaction Hours'...

ROUND(DATETIME_DIFF({Reacted Date & Time},{Request Date & Time},'minutes')/60,2)
 
If the request and reaction times are equal, 0.00 will be returned (correctly).  But then I embed it in an IF statement (simply to prevent #ERROR outputs if 1 of the input fields hasn't been populated yet)...
 
IF(AND({Reacted Date & Time},{Request Date & Time}), ROUND(DATETIME_DIFF({Reacted Date & Time},{Request Date & Time},'minutes')/60,2),"")
 
Now it works fine apart from the situation where a zero is returned, in which case the result is empty/blank.  Both date fields are present, so the first part of the IF statement is triggering, but for some reason the 0.00 value is replaced by empty/blank. 
 
Any ideas why?  
1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hm, could you try removing the `,""` at the end there?  So just:

IF(
  AND({Reacted Date & Time},{Request Date & Time}), 
  ROUND(
    DATETIME_DIFF(
      {Reacted Date & Time},
      {Request Date & Time},
      'minutes'
    )
    / 60,
    2
  )
)

I think that should show you the 0 like you want

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hm, could you try removing the `,""` at the end there?  So just:

IF(
  AND({Reacted Date & Time},{Request Date & Time}), 
  ROUND(
    DATETIME_DIFF(
      {Reacted Date & Time},
      {Request Date & Time},
      'minutes'
    )
    / 60,
    2
  )
)

I think that should show you the 0 like you want

that worked great thanks!  I thought I had to specify an else condition in these if statements but I guess not.  No idea why the else condition was triggered though since both the values of the AND statement were present.  Either way you fixed the bug so thanks again!