Help

Re: Format field as date

1092 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Anton_Preisinge
5 - Automation Enthusiast
5 - Automation Enthusiast

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

10 Replies 10

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

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
11 - Venus
11 - Venus

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
8 - Airtable Astronomer
8 - Airtable Astronomer

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 :

6EC455DD-DFF0-49B9-B687-911BFB8FD1AC.png

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
8 - Airtable Astronomer
8 - Airtable Astronomer

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

E4315844-4C3B-4B04-9117-D5ABA10371EB.jpeg

M_k
11 - Venus
11 - Venus

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
8 - Airtable Astronomer
8 - Airtable Astronomer

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

Brett_Snelgrove
6 - Interface Innovator
6 - Interface Innovator

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?

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

Great thank you! Worked a treat.

:thumbs_up: