Date Format Not Saving When Using Formula

Hello,

I am using a formula to extract a date from a string of text. It appears to be working, but if I leave the sheet and come back later it continually reformats the date from MM/DD/YY to YYYY,MM,DD. The target columns are formatted as dates, and the original column is a dropdown list. I can change the target column format to text, and then back to date and it will again reformat the date as MM/DD/YY.

The data in the original cell is a dropdown list and formatted as: 24Q1 - 6 (03/11-03/24)

I am using this formula to extract and convert the start date: =(VALUE(2024) + "," + VALUE(MID([Targeted Date]@row, 11, 2)) + "," + VALUE(MID([Targeted Date]@row, 14, 2)))

I am using this formula to extract and convert the end date: =(VALUE(2024) + "," + VALUE(MID([Targeted Date]@row, 17, 2)) + "," + VALUE(MID([Targeted Date]@row, 20, 2)))

I appreciate any help anyone can provide

Best Answer

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 02/26/24 Answer ✓

    @Dylan G In your formula, you are telling smartsheet to add commas between each date with this "," you can instead add "/" and it might work, or you can use these formulas below.

    START:

    =DATE(2024, VALUE(MID([Targeted Date]@row, 11, 2)), VALUE(MID([Targeted Date]@row, 14, 2)))

    END:

    =DATE(2024, VALUE(MID([Targeted Date]@row, 17, 2)), VALUE(MID([Targeted Date]@row, 20, 2)))

    Sincerely,

    Jacob Stey

Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 02/26/24 Answer ✓

    @Dylan G In your formula, you are telling smartsheet to add commas between each date with this "," you can instead add "/" and it might work, or you can use these formulas below.

    START:

    =DATE(2024, VALUE(MID([Targeted Date]@row, 11, 2)), VALUE(MID([Targeted Date]@row, 14, 2)))

    END:

    =DATE(2024, VALUE(MID([Targeted Date]@row, 17, 2)), VALUE(MID([Targeted Date]@row, 20, 2)))

    Sincerely,

    Jacob Stey

  • Dylan G
    Dylan G ✭✭

    Thanks @SteyJ ! That fixed it

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!