MM/DD/YYYY Date Format

RJGomez

Since there isn't a MM/DD/YYYY date format built into Smartsheet, I created a formula in order to create this date format. Please see below:

=IFERROR(IF(MONTH(Date@row) = 1, "01", IF(MONTH(Date@row) = 2, "02", IF(MONTH(Date@row) = 3, "03", IF(MONTH(Date@row) = 4, "04", IF(MONTH(Date@row) = 5, "05", IF(MONTH(Date@row) = 6, "06", IF(MONTH(Date@row) = 7, "07", IF(MONTH(Date@row) = 8, "08", IF(MONTH(Date@row) = 9, "09", IF(MONTH(Date@row) = 10, "10", IF(MONTH(Date@row) = 11, "11", IF(MONTH(Date@row) = 12, "12")))))))))))) + "/" + IF(DAY(Date@row) = 1, "01", IF(DAY(Date@row) = 2, "02", IF(DAY(Date@row) = 3, "03", IF(DAY(Date@row) = 4, "04", IF(DAY(Date@row) = 5, "05", IF(DAY(Date@row) = 6, "06", IF(DAY(Date@row) = 7, "07", IF(DAY(Date@row) = 8, "08", IF(DAY(Date@row) = 9, "09", IF(DAY(Date@row) = 10, "10", IF(DAY(Date@row) = 11, "11", IF(DAY(Date@row) = 12, "12", IF(DAY(Date@row) = 13, "13", IF(DAY(Date@row) = 14, "14", IF(DAY(Date@row) = 15, "15", IF(DAY(Date@row) = 16, "16", IF(DAY(Date@row) = 17, "17", IF(DAY(Date@row) = 18, "18", IF(DAY(Date@row) = 19, "19", IF(DAY(Date@row) = 20, "20", IF(DAY(Date@row) = 21, "21", IF(DAY(Date@row) = 22, "22", IF(DAY(Date@row) = 23, "23", IF(DAY(Date@row) = 24, "24", IF(DAY(Date@row) = 25, "25", IF(DAY(Date@row) = 26, "26", IF(DAY(Date@row) = 27, "27", IF(DAY(Date@row) = 28, "28", IF(DAY(Date@row) = 29, "29", IF(DAY(Date@row) = 30, "30", IF(DAY(Date@row) = 31, "31"))))))))))))))))))))))))))))))) + "/" + YEAR(Date@row), "")

You will need to substitute (Date@row) with the date cell you are referencing. It will display nothing in the cell if there is a error. Below is an example of how the formula works:

Date format in Smartsheet:

05/31/23

Date formula above will produce:

05/31/2023

I hope this helps!

damon.tackett

This is great RJ! Thank you!

I did have to make one format adjustment, but it might just be for the process I am using.

I had to change (Date@row) to ([Date]@row)

This has always seemed like such an easy upgrade to make so I didn't understand why it wasn't available but thank you for putting in the work to create the work around!