Convert Extracted date from a formula (YYYY-MM-DD) to MM/DD/YYYY

Zashouri

Hello, I have a data upload set from an excel file. It pulls the date data combined with time. I then used a formula to separate the two noted in column "Extracted Start Date" but I need the format to be in a "MM/DD/YYY" format. How can I accomplish this?

Carson Penticuff

What formula are you using to extract the date from your other column? It can likely be modified to work.

Zashouri

I am using:

=LEFT([Check in Date]@row, FIND(" ", [Check in Date]@row) - 1)

Carson Penticuff

Is "MM/DD/YYY" a typo? The following formula will give you MM/DD/YYYY. If you only want the last three digits of the year, replace the last "4" with "3".

=IF([Check in Date]@row <> "", MID([Check in Date]@row, 6, 2) + "/" + MID([Check in Date]@row, 9, 2) + "/" + LEFT([Check in Date]@row, 4), "")

Zashouri

Thanks! Yes it was an error. It worked!

However now when trying to use a report, it wont recognize dates as a filter?

Carson Penticuff

That is more of an issue. Smartsheet does not support MM/DD/YYYY as a date format, which is unfortunate, as that is my preferred format as well. To my knowledge, it is not possible to display the MM/DD/YYYY format and have the column treated as dates. One possibility is to create an additional column, formatted as date, and use that as a filter. Here are the accepted date formats. (At least in my locale)

Zashouri

Thanks. How would I copy/transfer those dates to that new column?

Carson Penticuff

You can just create a new column and apply the formula you were previously using to that one, or apply the formula I provided to the new one and use your previous formula in the existing column... either way should work.

Zashouri

So the above didnt work. Can I use the date formula function? If so, trying to use a formula similar to the below but not working:

=DATE(IF([Check in Date]@row <> "", VALUE(MID([Check in Date]@row, 6, 2) + "/" + VALUE(MID([Check in Date]@row, 9, 2) + "/" + VALUE(MID([Check in Date]@row, 3, 2), "")))))

Carson Penticuff

What about it is not working? Is it that the report isn't picking up the date column to filter? Did you try using your original formula in your original column and then creating a new column with the formula I provided? It should work that way.

The problem you will continue to run into, is that the format you want to use to display your date is not available. You can either have the date displayed in this format but treated as text, or you can have it treated as a date and displayed with one of the built-in formats.

I think I will see about requesting the additional date format be supported, it would be my preference as well.