Help

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

IF + DATETIME_DIFF Formula Help! πŸ™πŸΌ

Solved
Jump to Solution
1293 4
cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 
Spruce
6 - Interface Innovator
6 - Interface Innovator

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!!
Devon
1 Solution

Accepted Solutions
Josh_Colina
6 - Interface Innovator
6 - Interface Innovator

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}
  )
)

See Solution in Thread

4 Replies 4
Josh_Colina
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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!

Devon
Spruce
6 - Interface Innovator
6 - Interface Innovator

@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!

Spruce_0-1696113476610.png

 

Devon
Josh_Colina
6 - Interface Innovator
6 - Interface Innovator

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'
  )
)