Format field as date

[Anton_Preisinge]

Silly question here but my eyes are crossed trying to sort it out.

Is there any reason the field won’t allow me to format the following formula as a date? I even tried datetime_format bracketed around the entire formula below and it still said it wasn’t recognized as a date. I’ve also tried to convert the information from this field in a second formula field and formatting that as a date to no avail.

IF({Completed Chores}=BLANK(),DATETIME_FORMAT(TODAY(),‘M/DD/YYYY’),IF(PRN = 1, DATETIME_FORMAT(TODAY(),‘M/DD/YYYY’),DATETIME_FORMAT(DATEADD({Calculated last date completed}, {Frequency}, ‘days’),‘M/DD/YYYY’)))

The output looks like a date but it won’t format as a date so I can’t filter or group on it. I figure I must be missing something but I can’t spot it for the life of me.

TIA!

Anton

[Julian_Kirkness]

Hi Anton

DATETIME_FORMAT() returns it’s result as a string (text) value and this is why you can’t format it as a date. If you get rid of this function you should be able to format as a date and use filters and groups with it.

Hope this helps.

Julian

[Anton_Preisinge]

Oh man, of course it does! Thank you for spelling it out for me, now the solution is obvious…I just still need my training wheels :stuck_out_tongue: Thanks for taking the time to help!

[M_k]

Hi

I am having trouble with a date formula:
DATETIME_FORMAT({name of date field},‘llll’)

I tried it with the name of the field DATE ISSUED:
DATETIME_FORMAT({DATE ISSUED},‘llll’).

I want to create a formula that will format the date in the DATE ISSUED field like this

Thurs, May 31, 2018

The time stamp is not necessary.

I clicked the field, DATE ISSUED, (when I set this field up I chose Friendly Format but I did not choose the time option), in MacBook, I then selected customized field and then I selected the formula option, but it did not work. I am not sure what I did wrong.

The general error message in the formula option is:
“Sorry, there was a problem saving this field. Can’t save field because it causes a circular reference.”

I get this error message in the formula tab, after I paste the formula:
“Compute a value in each record based on other fields in the same record…”

I get this message in the formatting tab:
“Your field is not fully configured. You need to have a valid field configuration in the formula tab before adding formatting options.”

Will the date formula also work whenever I create new records and any records that are copied into the base?

Thank you for your help.

PS I was not sure how to add attachment/screenshot.

[Ptt_Pch]

I think, if I understand you correctly that in order to use your formula, you’ll need to create another field which will kind of « translate » the date you put in your DATE ISSUED field into the format you want.
So you actually need 2 fields. One with the date and an other one with the formula.

I would try this in the formula field:

DATETIME_FORMAT({DATE ISSUED},'ddd, MMMM D, YYYY')

It will give you something like this :

Edit :
If you create new records and put a date in the DATE ISSUED, the date wil be automatically transformed in the desire format in the formula field (because the DATE ISSUED Is directly referenced in the formula) .
If you leave the DATE ISSUED field empty, it might give you, as a result an #ERROR but this can be changed by adapting the formula in the formula field.

[Ptt_Pch]

For the screenshot here, you can just drag and drop it or use the upload image menu you can find here :

[M_k]

Hi

Thank you very much for your help with the date formula. It works like a charm.

Thank you!
I am now a happy Airtable user. :grinning:

[Ptt_Pch]

My pleasure :grinning_face_with_smiling_eyes: !
Always glad to help when I can :grinning_face_with_smiling_eyes:

[Brett_Snelgrove]

Hi there,

I would love some help. I am having an issue with have a formula generating a date recognising it as such so that I can group via it.

Here is my formula:

IF(AND({MTH / YR}>=DATETIME_PARSE(“1 January 2018”,‘D MMMM YYYY’),{MTH / YR}<=DATETIME_PARSE(“1 December 2018”,‘D MMMM YYYY’)),DATETIME_FORMAT(DATETIME_PARSE(“1 December 2018”,‘D MMMM YYYY’),‘MMMM YYYY’),
IF(AND({MTH / YR}>=DATETIME_PARSE(“1 April 2019”,‘D MMMM YYYY’),{MTH / YR}<=DATETIME_PARSE(“1 March 2020”,‘D MMMM YYYY’)),DATETIME_FORMAT(DATETIME_PARSE(“1 April 2019”,‘D MMMM YYYY’),‘MMMM YYYY’),0))

Any clue what is going wrong here?

[W_Vann_Hall]

So how is it failing?

When I cut-and-paste your formula – correcting for the fancy quotes introduced by the forum software – it works: I get such responses as “December 2018,” “April 2019,” and so forth. (I’m assuming {MTH / YR} is an Airtable date field…)

If the problem is that you can’t use Airtable’s field formatting to select a date format, that’s correct: By wrapping your output in a DATETIME_FORMAT() function, you’re converting the datetime value to a string in the formula. If you want the result to be field-formattable as a date, leave off DATETIME_FORMAT().

I also would eliminate the ‘,0’ “else” clause at the end; it’s unnecessary, and if you use the output of the formula in another calculation, it may not behave as you expect.

Here’s the version of your formula I used; the only changes I made were to remove curly quotes and to eliminate the final clause. You can copy-and-paste this into your formula field – including indentation; Airtable don’t care – and it should work.

IF(
    AND(
        {MTH / YR}>=DATETIME_PARSE(
            "1 January 2018",
            'D MMMM YYYY'
            ),
        {MTH / YR}<=DATETIME_PARSE(
            "1 December 2018",
            'D MMMM YYYY'
            )
        ),
    DATETIME_FORMAT(
        DATETIME_PARSE(
            "1 December 2018",
            'D MMMM YYYY'
            ),
        'MMMM YYYY'
    ),
    IF(
        AND(
            {MTH / YR}>=DATETIME_PARSE(
                "1 April 2019",
                'D MMMM YYYY'
                ),
            {MTH / YR}<=DATETIME_PARSE(
                "1 March 2020",
                'D MMMM YYYY'
                )
            ),
        DATETIME_FORMAT(
            DATETIME_PARSE(
                "1 April 2019",
                'D MMMM YYYY'
                ),
            'MMMM YYYY'
            )
        )
    )