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

Find more posts tagged with

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.