Converting Drop-down Date to a Date formatted value

Question

First off, I am using Control Center to build out projects that are dependent on a central worksheet, "Weeks of…" as a drop-down source list. The "Weeks of…" is contained in a Portfolio folder in the Admin workspace.

Secondly, I have a worksheet, "Weeks" that contains a number of dates, DATE. In this worksheet I have an automation with a checkbox to indicate whether the dates are in a certain range. If they are in that range they are checked. A Data Shuttle job then picks up the dates within the range and creates an Excel file that is attached to the "Weeks of…" worksheet. A Upload job replaces the dates of "Weeks of…" so the list of dates changes. However, to allow the dates to be in the project drop-down lists, "Week of…"  they have to be formatted as TEXT/NUMBER. This changes a date of 2/9/2026 to 2026-02-09.

Third, on the CC project worksheet the "Week of…" drop-down then lists 2026-02-09, 2026-02-16, etc… I need to change this text formatted date back to date formatted value so that I can do calculations like… Today - Week of…, 02/17/26 - 02/09/26.

I have searched through post but cannot seem to find anything that seems to work. Any help is greatly appreciated.

Paul Newcome · Accepted Answer

If your text string is formatted like so: "2026-02-09", the formula to convert it into a date type data would be…

=DATE(VALUE(LEFT([Dropdown Column Name]@row, 4)), VALUE(MID([Dropdown Column Name]@row, 6, 2)), VALUE(RIGHT([Dropdown Column Name]@row, 2)))

Paul Reeves · Accepted Answer

I believe I have solved my problem. I am breaking down the Text/Number date into Month, Day, Year helper columns with the Value(). I am then creating a Date column with the Date(). I think the main issue I had was I did not pick up the structure of the Date(Year, Month, Date) format. Once I got out of my way then I was able to do what I needed.