Re: Datetime Format IF Help

544 0
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

I am trying and very close to getting this to work

IF(DATETIME_FORMAT({Last Time Done}, ‘YYYY’ = 0000), DATETIME_DIFF(TODAY(), {Last Time Done},‘days’), “Never Done”)

I can’t get it to say neverd one when last time done year matches 0000

2 Replies 2
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi! I figured out that there are a few things you can change in your code:

  1. Logic
    The ‘IF’ clause work in this way:
    IF(condition, what to show if condition is true, what to show if condition is false)
    Therefore, you should put “Never Done” at the first bit instead of the final bit if you want “Never Done” to be shown when the year of {Last Time Done} matches 0000.

  2. Syntax
    In your code, the ‘)’ is put at a wrong place. You should first close the DATETIME_FORMAT to make it a string,THEN you compare the string to “0000”. Also, I think you can “quote” 0000 to show that it is a string but not a number. (However leaving it unquote also doesn’t matter)

To sum up, use the code below instead:

IF(DATETIME_FORMAT({Last Time Done},'YYYY')="0000","Never Done",DATETIME_DIFF(TODAY(),{Last Time Done},'days'))

Actually, I think making the year to become 0000 is quite troublesome :joy: There is a way for you to show "Never Done" if the {Last Time Done} date is blank:
IF({Last Time Done}=BLANK(),"Never Done",DATETIME_DIFF(TODAY(),{Last Time Done},'days'))

Just for reference. Have fun!

Good call. If a date field is blank, any date-related functions will cause the formula to throw an error. A slightly easier way to check for an empty date field is to wrap the NOT() function around the field reference itself. Keeping the same logical order, that variation would look like this:

IF(NOT({Last Time Done}), "Never Done", DATETIME_DIFF(TODAY(),{Last Time Done},'days'))

You could also remove NOT(), and reverse the order of the other two arguments:

IF({Last Time Done}, DATETIME_DIFF(TODAY(),{Last Time Done},'days'), "Never Done")