Month, Day, Year Date Conversion to MM/DD/YY?

Jerexify

Good afternoon,

I'm working with a sheet where the data is imported from Excel, and the Date column is actually the primary column on the Smartsheet tracker. Given that it's the primary, it's obviously not a date column, and since it's imported, it's being imported in this format: March 4, 2024 5:10pm

Now, I created a column right next to it called Date Helper, and if I copy the contents of a cell in the Date column and paste it into the corresponding cell in the Date Helper column (which is actually a Smartsheet Date column-type), then it automatically converts it to MM/DD/YY format, which is what I need.

Is there a way to get it to do this automatically moving forward? I tried an Index formula, but it copies the whole string of text from the Date column and doesn't convert it. I can't use automation, because the Record a Date function only records today's date/time, and that's not what I need.

Any ideas?

Thank you.

DKazatsky2

Hi @Jerexify,

I have a rather unwieldly solution but I think it will give you what you are after. You will need 3 "helper" columns for Year, Month, and Day with the following formulas.

Year (helper) --> =VALUE(MID([Date String]@row, FIND(",", [Date String]@row) + 2, 4))

Month (helper) --> =IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "January", 1, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "February", 2, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "March", 3, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "April", 4, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "May", 5, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "June", 6, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "July", 7, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "August", 8, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "September", 9, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "October", 10, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "November", 11, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "December", 12))))))))))))

Day (helper) =VALUE(SUBSTITUTE(MID([Date String]@row, FIND(" ", [Date String]@row), FIND(",", [Date String]@row) - FIND(" ", [Date String]@row)), " ", ""))

Date --> =DATE([Year (helper)]@row, [Month (helper)]@row, [Day (helper)]@row)

I know it's not elegant, but it's functional.

Hope this helps,

Dave

Kelly Moore

Hey @Jerexify

Here is similar logic to @DKazatsky2 approach above but strung together in one long formula. The advantage to the one above is if something breaks it is more clear what is broken. However, if you are constrained by the number of columns then this formula below may be of help. Just to help you decipher the formula below, I highlighted the 'day' portion of the DATE function in bold.

=DATE(VALUE(MID(Date@row, FIND(",", Date@row) + 2, 4)), IF(CONTAINS("January", Date@row), 1, IF(CONTAINS("February", Date@row), 2, IF(CONTAINS("March", Date@row), 3, IF(CONTAINS("April", Date@row), 4, IF(CONTAINS("May", Date@row), 5, IF(CONTAINS("June", Date@row), 6, IF(CONTAINS("July", Date@row), 7, IF(CONTAINS("August", Date@row), 8, IF(CONTAINS("Septermber", Date@row), 9, IF(CONTAINS("October", Date@row), 10, IF(CONTAINS("November", Date@row), 11, IF(CONTAINS("December", Date@row), 12)))))))))))), VALUE(MID(Date@row, FIND(" ", Date@row) + 1, FIND(",", Date@row) - FIND(" ", Date@row))))

Kelly

DKazatsky2

Hi @Jerexify,

I have a rather unwieldly solution but I think it will give you what you are after. You will need 3 "helper" columns for Year, Month, and Day with the following formulas.

Year (helper) --> =VALUE(MID([Date String]@row, FIND(",", [Date String]@row) + 2, 4))

Month (helper) --> =IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "January", 1, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "February", 2, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "March", 3, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "April", 4, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "May", 5, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "June", 6, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "July", 7, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "August", 8, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "September", 9, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "October", 10, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "November", 11, IF(LEFT([Date String]@row, FIND(" ", [Date String]@row) - 1) = "December", 12))))))))))))

Day (helper) =VALUE(SUBSTITUTE(MID([Date String]@row, FIND(" ", [Date String]@row), FIND(",", [Date String]@row) - FIND(" ", [Date String]@row)), " ", ""))

Date --> =DATE([Year (helper)]@row, [Month (helper)]@row, [Day (helper)]@row)

I know it's not elegant, but it's functional.

Hope this helps,

Dave

Kelly Moore

Hey @Jerexify

Here is similar logic to @DKazatsky2 approach above but strung together in one long formula. The advantage to the one above is if something breaks it is more clear what is broken. However, if you are constrained by the number of columns then this formula below may be of help. Just to help you decipher the formula below, I highlighted the 'day' portion of the DATE function in bold.

=DATE(VALUE(MID(Date@row, FIND(",", Date@row) + 2, 4)), IF(CONTAINS("January", Date@row), 1, IF(CONTAINS("February", Date@row), 2, IF(CONTAINS("March", Date@row), 3, IF(CONTAINS("April", Date@row), 4, IF(CONTAINS("May", Date@row), 5, IF(CONTAINS("June", Date@row), 6, IF(CONTAINS("July", Date@row), 7, IF(CONTAINS("August", Date@row), 8, IF(CONTAINS("Septermber", Date@row), 9, IF(CONTAINS("October", Date@row), 10, IF(CONTAINS("November", Date@row), 11, IF(CONTAINS("December", Date@row), 12)))))))))))), VALUE(MID(Date@row, FIND(" ", Date@row) + 1, FIND(",", Date@row) - FIND(" ", Date@row))))

Kelly

Jerexify

@DKazatsky2 and @Kelly Moore Thank you both so much for your help! The formulas worked beautifully and did exactly what I needed, and are definitely nothing I would have ever thought about. lol