Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

YYYY-MM Date Format

cancel
Showing results for 
Search instead for 
Did you mean: 
airship
7 - App Architect
7 - App Architect

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.

10 Comments
Bill_French
17 - Neptune
17 - Neptune

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:

image

airship
7 - App Architect
7 - App Architect

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:

Screen Shot 2019-08-21 at 10.07.41.png

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.

Screen Shot 2019-08-21 at 10.07.23.png

Thank you.

BillFrench
7 - App Architect
7 - App Architect

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
13 - Mars
13 - Mars

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

Screenshot 2019-08-21 at 19.53.09.png

JB

Bill_French
17 - Neptune
17 - Neptune

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
7 - App Architect
7 - App Architect

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
17 - Neptune
17 - Neptune

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
13 - Mars
13 - Mars

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
7 - App Architect
7 - App Architect

@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
5 - Automation Enthusiast
5 - Automation Enthusiast

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