Format field as date


#1

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


#2

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


#3

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!


#4

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.


#5

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.


#6

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


#7

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:


#8

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