Skip to main content
Solved

Formatting of Dates in IF Formulas

  • September 20, 2020
  • 2 replies
  • 28 views
Brite_Admin
Forum|alt.badge.img+11

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.

Best answer by kuovonne

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

2 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • September 20, 2020

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'
))
Brite_Admin
Forum|alt.badge.img+11
  • Brite_Admin
  • Author
  • Inspiring
  • September 21, 2020

Thanks kuovonne! I appreciate the help.

Terms & ConditionsAccessibility statement