Date formatting to MM/DD/YYYY

Sbott
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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Sbott
    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
    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
    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
    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
    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
    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
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com