Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Formatting of Dates in IF Formulas

Topic Labels: Formulas
Solved
Jump to Solution
318 2
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a formula where this year’s anniversary is based on the date of Marriage but it returns an ERROR if the marriage date is blank and I would like it show as a blank instead. Here is my old formula.

DATETIME_PARSE(
DATETIME_FORMAT(
{Marriage Date},
‘MMMM D’
)&
’ '&
YEAR(
TODAY()
),
‘MMM D YYYY’
)

II have tried using an IF statement and it does work for the blank marriage dates but for some reason, the ones with Marriage dates are no longer formatted as MMM/D/YYYY and instead show 2020-03-31T00:00:00.000Z. .
Here is my new Formula with the wrong formatting:
IF({Marriage Date},DATETIME_PARSE(
DATETIME_FORMAT(
{Marriage Date},
‘MMMM D’
)&
’ '&
YEAR(
TODAY()
),
‘MMM D YYYY’
),"")

I can’t figure out where to move the date formatting so it shows up as 3/31/2020

Any help would be appreciated.

1 Solution

Accepted Solutions

Remove the final part of the IF function—the final comma and the two quote characters. They are turning your date object into a date string.

IF({Marriage Date},DATETIME_PARSE(
DATETIME_FORMAT(
{Marriage Date},
'MMMM D'
)&
' '&
YEAR(
TODAY()
),
'MMM D YYYY'
))

See Solution in Thread

2 Replies 2

Remove the final part of the IF function—the final comma and the two quote characters. They are turning your date object into a date string.

IF({Marriage Date},DATETIME_PARSE(
DATETIME_FORMAT(
{Marriage Date},
'MMMM D'
)&
' '&
YEAR(
TODAY()
),
'MMM D YYYY'
))

Thanks kuovonne! I appreciate the help.