YYYY-MM Date Format

[airship]

Keeping a log of the magazines that I read, and it would be nice if we could format the date to exclude the day in a “YYYY-MM” format. So the “January 2019 issue” could be read in that format or a user selectable “2019-01” or “01/2019”. My current workaround is just use the 1st day of the month, meaning “2019-01-01” and I just visually ignore the day part. Some people might also just want the year, but I guess that could be done with single select or single line text. Thanks.

[Bill_French]

Maybe I’m missing something obvious (I’m old) - if you simply need a representation of the data in a more concise and friendlier format, create a new column and use a formula to render what you really want to see.

YEAR({Create Date}) & "-" & IF(MONTH({Create Date}) < 10, "0" & MONTH({Create Date}), MONTH({Create Date}))

That formula (which is near the top of my technical range) displays this:

[airship]

Wow @Bill.French, you are a wizard. I’m not adept at formulas, and must not be doing something right. I’m pasting that formula in, but I’m getting an error message:

I then tried renaming “Create Date” to “ISSUE DATE” which is how my column is named, but still having a problem, citing a circular reference.

Thank you.

[BillFrench]

The formula needs to be in a formula field that is not the same name as ISSUE DATE. Create a formula field with a name like YR-MONTH.

[JonathanBowen]

Hi @airship, @Bill.French - nothing wrong with Bill’s solution, it will work fine, although there is a simpler format available which might be better:

DATETIME_FORMAT({Date}, 'YYYY-MM')

JB

[Bill_French]

That is far more elegant - me like. I have some tables to update. As I mentioned in another thread, I’m flying right at the top of my formula expertise range.

[airship]

Thank you @JonathanBowen, that is more elegant code. Pardon me being candid, in the end though I guess the formula requires a reference to produce the formula result. I’m not certain if having 2 date columns as a workaround is necessarily better than 1 column where I visually ignore the day part of the date. I have to experiment with this further.

I was hoping as a product suggestion that Airtable devs might consider offering more date formats. But I am ignorant of their development timeline, and I appreciate this workaround in the meantime. Thank you again!

[Bill_French]

airship:

having 2 date columns as a workaround is necessarily better than 1 column where I visually ignore the day part

I agree; this is a workaround - the formula is elegant but the mere fact that we must pull stunts like this annoys the crap out of me. This is precisely the issue I often mention here in the forum - going around the barn; it tends to create complexities that future custodians of the system will not understand.

Indeed, this is the moment where the developers need to be diligent about separating the rendering engine from the data engine. This would be an easy lift if dates could be masked using custom tokens (like Google Sheets for example). Formats are apparently poured in concrete but they should be rendered with a format mask - all of them - even the default formats they currently provide.

+1 on the wish list for me.

[JonathanBowen]

airship:

I’m not certain if having 2 date columns as a workaround is necessarily better than 1 column where I visually ignore the day part of the date.

For sure. The solutions above don’t solve your issue and I know this is a “product suggestions” post rather than a “how do I do this?” post, just a workaround, but it might not be the best option for you, I agree.

[airship]

@Bill.French Concur. Thank you.

@JonathanBowen I appreciate the workaround. Will play with it more and see if it grows on me.

Thank you!

[Berber]

+1, there are lot of situations where are specific date is not required