Date formatting to MM/DD/YYYY
Good afternoon. I need to format a DOB column to the MM/DD/YYYY format. Currently, the column type is set to Date and restricted to dates only. I don't see that as an option in the Smartsheet Date Format drop-down list. I have spent over an hour looking for the answer in this community and can't find it. I am now down to the wire to get this changed. Is there a formula that can be used? Or am I missing the obvious?
I really appreciate any help you can provide.
Shauna
Answers
-
In the top toolbar is a picture of a calendar. Clicking on that should give you some options for date formatting.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks @Paul Newcome . Unfortunately, the formatting option that I am needing is not listed in that list. I am looking for all numeric MM/DD/YYYY.
Shauna
-
I was able to create a second date column and use a formula to reference the original to recreate the date correctly.
=JOIN(DAY(BirthDate@row) + "/" + MONTH(BirthDate@row) + "/" + YEAR(BirthDate@row))
-
Thanks @JamesB . That did not work either as I am receiving an Invalid Data Type error. This data is coming from a form where the user fills out their BirthDate. I assume I am getting that error because I don't have the data broken out into MONTH, DAY, YEAR columns.
I am surprised this is not a format already available or an easier formula. I realize this MM/DD/YYYY format is not the most common. It would be great to have the ability to easily change the format.
Shauna
-
The format is available based on your regional settings in your personal settings area of your Smartsheet, however it will affect your entire experience not just an individual sheet. I am in the US so my date format is already MM/DD/YYYY. Sounds like another post for the suggestion box.
-
@JamesB That is where I am confused. I am in the US as well and my regional preference is English (US) but the date is MM/DD/YY. How did you set your regional preference to be the YYYY instead?
-
@Sbott I only get the 4 digit year when I use the formula. Like you suggested, you could create your Form with dropdown lists for the end user to choose a month number, then day number, then year number. From there, you can use a formula to join them into 1 date column.
=JOIN(Month@row + "/" + Day@row + "/" + Year@row)
-
I just took a deeper dive into it and it looks like that format is not available. You are going to have to use a formula to output a text string that just looks like a date. You will need to reference the original date column in any calculations where you need to use the actual date.
=DAY(BirthDate@row) + "/" + MONTH(BirthDate@row) + "/" + YEAR(BirthDate@row)
And if you want a two digit day and month:
=IF(DAY(BirthDate@row)< 10, "0", "") + DAY(BirthDate@row) "/" + IF(MONTH(BirthDate@row)< 10, "0", "") + MONTH(BirthDate@row) + "/" + YEAR(BirthDate@row)
Regardless of which of the two above you use, it will be stored on the back-end as a text string.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives