IF + DATETIME_DIFF Formula Help! 🙏🏼

[Spruce]

I have a lot to learn in terms of building formulas, and I'm found myself stuck and not sure what I'm missing.

What I'm trying to accomplish:

1. If the Completed column checkbox is ticked, display "Completed"
2. If the checkbox is not ticked, display the time difference between the 'Ready for Pickup' Timestamp column timestamp and now in days.

My current (not working) formula:

IF({Completed}=TRUE, "Completed",(DATETIME_DIFF(NOW(), {'Ready for Pickup' Timestamp}, 'days'))

Thank you!!

[Josh_Colina · Accepted solution]

I think part of where this formula isn't working is in designating "TRUE" - you can actually accomplish this with the checkbox field just by using 

IF({Completed}, "Completed")

or, if there is something at all in the Completed checkbox field (i.e. it is checked), return "Completed." I would also use TONOW() rather than the DATETIME_DIFF. Here's what I used!

IF(
  {completed},
  "Completed",
  TONOW(
    {Ready for Pickup Timestamp}
  )
)

[Spruce]

Thank you so much, Josh! I like the TONOW idea, but I ended up going with the DATETIME_DIFF because it let me specify showing the units in days which should make my email send automation a bit easier.

Thank you so so much for the quick reply!

[Spruce]

@Josh_Colina , oh uh, I spoke too soon! The formula I thought was working is actually displaying blank cell instead of a number of days (see screenshot). The "completed" part of the formula is working. Any tips for this?

Thank you!

[Josh_Colina]

Hi Spruce! Here's what I tried that seems to be working for me - unsure why exactly but NOW() seems to work better than TODAY()

IF(
  {Completed},
  "Completed",
  DATETIME_DIFF(
    NOW(),
    {Ready for Pickup Timestamp},
    'd'
  )
)