Date formatting to MM/DD/YYYY

Sbott

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

Paul Newcome

In the top toolbar is a picture of a calendar. Clicking on that should give you some options for date formatting.

Sbott

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

JamesB

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

Sbott

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

JamesB

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.

Sbott

@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?

JamesB

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

Paul Newcome

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.