Date Format Not Saving When Using Formula

Dylan G

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

SteyJ

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

SteyJ

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

Dylan G

Thanks @SteyJ ! That fixed it