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

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.

Tags:

Best Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    edited 03/06/24 Answer ✓

    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

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    edited 03/06/24 Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!