Skip to main content
Solved

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

  • April 2, 2024
  • 6 replies
  • 40 views
TheMortgageMeister
Forum|alt.badge.img+11

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

 

Best answer by Sho

 

How about this.

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

 

6 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • Answer
  • April 2, 2024

 

How about this.

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

 

TheMortgageMeister
TheTimeSavingCo
Forum|alt.badge.img+31

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

 

Adam, Airtable Consultant - thetimesaving.company
TheMortgageMeister
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • April 3, 2024

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" ) )
TheMortgageMeister
TheMortgageMeister
Forum|alt.badge.img+11

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?

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • April 4, 2024

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.

TheMortgageMeister
TheMortgageMeister
Forum|alt.badge.img+11

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.


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.

Terms & ConditionsAccessibility statement