Help

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

Cannot format Date with conditional formula if Date is Blank (Formula Formatting)

Topic Labels: Formulas
Solved
Jump to Solution
1297 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Mike_Shaw1
6 - Interface Innovator
6 - Interface Innovator

I'm trying to have the formula with a dateadd calculation be blank of a certain field is blank. However if I put the  DateFormat function around the whole If formula it will either result in an error if the input is Blank or if any option could be blank it will not allow the Formula Formatting as a Date field either way. How can AT recognize a formula field as a date field if the date could be blank based on a conditional formula or related blank field???

 
 
DATETIME_FORMAT(IF({Weekday Planner}=BLANK(),BLANK(),DATEADD(TODAY(),(({Auto Date 2}-(WEEKDAY(TODAY())))*24)+15,"hours")),'MM/DD/YYYY h:hm A')

 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

 

How about this.

IF(
  {Weekday Planner},
  DATETIME_FORMAT(
    DATEADD(
      TODAY(),
      (({Auto Date 2}-WEEKDAY(TODAY()))*24)+15,
      "hours"
    ),
    'MM/DD/YYYY h:hm A'
  )
)

 

See Solution in Thread

6 Replies 6
Sho
11 - Venus
11 - Venus

 

How about this.

IF(
  {Weekday Planner},
  DATETIME_FORMAT(
    DATEADD(
      TODAY(),
      (({Auto Date 2}-WEEKDAY(TODAY()))*24)+15,
      "hours"
    ),
    'MM/DD/YYYY h:hm A'
  )
)

 

TheTimeSavingCo
18 - Pluto
18 - Pluto

Edit: Whoops, sorry @Sho , didn't mean to post the exact same thing as you!  I think I opened the tab when there were no replies and then went to look for coffee or something
===

Try this:

 

  IF(
    {Weekday planner},
    DATETIME_FORMAT(
      DATEADD(
        TODAY(),
        (({Auto Date 2}-(WEEKDAY(TODAY())))*24)+15,
        "hours"
      ),
      'MM/DD/YYYY h:hm A'
    )
  )

 

kuovonne
18 - Pluto
18 - Pluto

The DATETIME_FORMAT() function converts a date to a text string. If you want Airtable to recognize the new date as a date, omit the DATETIME_FORMAT() function.

IF(
  {Weekday Planner},
  DATEADD(
    TODAY(),
    (({Auto Date 2} - (WEEKDAY(TODAY())))*24)+15,
    "hours"
  )
)
Mike_Shaw1
6 - Interface Innovator
6 - Interface Innovator

It does work!  I am curious about this formula you all made. Does it help that there is not an actual condition such as {weekday planner}=""   ?   What does it mean when it is simply If({weekday planner}, X) and there is no other option. Does it mean if it exists or is not blank?, and there's no close out for if/then, is it assumed as well?

Sho
11 - Venus
11 - Venus

Although not mentioned in the reference,
If only the field name is indicated in the condition, the value of the field is considered False whether it is FALSE, empty or 0.
The value of FALSE can then be omitted.

Mike_Shaw1
6 - Interface Innovator
6 - Interface Innovator

Thanks, that must be common to use in coding but I'm more of an excel guy, not even VBA. I don't even format anything, I set up these conditionals linearly and read them like a sentence! I need to learn your ways.