Issue With Date Formula

[M_k]

Hi

I keep getting an error when I use this formula:

DATETIME_FORMAT({DATE ISSUED},‘MM-DD-YYYY’)

This is what I am trying to do:

Make a second formula field using DATETIME_FORMAT({DATE ISSUED},‘MM-DD-YYYY’)^ and then copy the values for all your imported CSV entries into your original “Date Issued” field. The formula field you’re using now will then produce the result you’re after. After you do this you can delete your second formula field, and convert “Date Issued” into a date field."

Basically, I want to convert a text field with this date format:
1955-02-21

To this date format:
Fri, November 19, 1976

Here is a screenshot of my base setup, the first DATE ISSUED is a different formula. It’s the second DATE ISSUED formula that I am trying to get working:

I would appreciate any help.

Thank you,
Mary

[Elias_Gomez_Sai]

DATETIME_FORMAT expects a date (as you can see in the yellow box), but you have a string. You can use first DATETIME_PARSE to convert it to date.

Date functions:

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

[M_k]

Hi @Elias_Gomez_Sainz

I will give this a try.

Thank you,
Mary

[Justin_Barrett]

Looking at your earlier screenshot, you’ve got “smart quotes” around the format string in the formula. Those will throw an error. Replace them with straight quotes and it should work.

Also be aware that if the date field you’re trying to format has no date, Airtable will throw an error. When formatting dates in fields that may sometimes be empty, it’s helpful to wrap an IF() function around the format to ensure that blank date fields won’t lead to errors. Looking at your screenshot again, try this out:

IF({DATE ISSUED 2}, DATETIME_FORMAT({DATE ISSUED 2, 'dddd, MMMM D, YYYY'}))

[M_k]

Hi @Justin_Barrett

Thank you for your help.

I was trying to use the IF formula, but I keep getting an error.

Trying to set up a formula should not be so difficult.

These are the steps that I followed:

1. I made sure the quotes are single and straight.

2. Then I created the DATE ISSUED 2 field as text, I also tried the date field type too, but that didn’t work.

3. I then set up another field for the formula and I named it FORMULA.

4. Then I just copied and pasted the formula, but I got an error. I also made sure that I had the same field name in the formula as I had as the field name.

5. I am not sure why it does not work.

I have included a screenshot of the setup and error showing.

Thank you,
Mary

P.S. I tried this formula: DATETIME_FORMAT({DATE ISSUED},‘ddd, MMMM D, YYYY’) and it worked (changed the field name to what’s in the formula.). This is the formula that I am using now, but I would like to change it to the one you provided, so I don’t get the error in the field, when there is nothing in the DATE ISSUED field.

MARY

[Justin_Barrett]

The main problem in the screenshot is the placement of your closing curly brace for the second field reference. It should be after the “2” in {DATE ISSUED 2}, but it’s currently sitting after the formatting string. {DATE ISSUED 2} also needs to be a date field, not text. This should work once those changes are made:

IF({DATE ISSUED 2}, DATETIME_FORMAT({DATE ISSUED 2}, 'dddd, MMMM D, YYYY'))

[Elias_Gomez_Sai]

I think you wrote the field names by yourself. If you choose the fields from the suggestion list, the curly braces are automatic.

[M_k]

Hi @Justin_Barrett and @Elias_Gomez_Sainz

Thank you both very much.

I got all date formulas to work properly.

Thank you,
Mary