Converting text/number to a date for a formula in a date column

jjesmith

I have a date in one row of a Text/Number column (call it Inputs 24). I want to use this cell in a formula in a date column to calculate Due Dates for different tasks.

Inputs 24 itself has a Join/Collect formula to gather the date as it could be in multiple rows of another column.

=JOIN(COLLECT([Start Date]:[Start Date], [Start Date]:[Start Date], @cell <> ""))

How do I take the text (which I'm already doing with the Join/collect formula), make it a date and then add and subtract days from it to become a different date?

Kelly Moore

Hey @jjesmith

Is it possible to show a screenshot of some of your cells Inputs 24 column to see what your data looks like? What is the format of your data? A few cells please to show if there is variability in the data set

jjesmith

The data that will always be in the one cell called Inputs24 is a date but this is a number/text column. This info is populated from an update request.

Then I want dates for tasks to automatically appear in the Due Date column which is a Date column. These will be different based on the task. For example, = to the Inputs24 cell

SS 2.PNG

SS 1.PNG

, 2 weeks before the Inputs24 cell, 1 week before the Inputs24 cell. Different formulas to go into each of the cells depending on the task.

jjesmith

I believe this is a relatively simple formula but the complication is that the first date is from a "number/text" cell and then I'm trying to calculate a date for a "date column".

marc4

This great explanation shows how to convert a "date" in a text column into a real date. You'll have to make a few changes for your format of xx/xx/xx but it should give you the outline of what you need to do.

https://community.smartsheet.com/discussion/95352/converting-a-text-month-year-into-a-date

You can use the left, mid and right functions to grab the digits from the text column and then the =DATE(VALUE()) to convert it to a date. Then you can calculate the due date.

I've used it on a number of sheets.

/marc

Kelly Moore

Hey @jjesmith

Marc's link is a great reference. To insure it provides exactly what you need, here's the formula that converts your Input24 into a useable date for use in your other cells

=DATE(2000 + VALUE(RIGHT(Inputs24, 2)), VALUE(LEFT(Inputs24, FIND("/", Inputs24) - 1)), VALUE(MID(Inputs24, FIND("/", Inputs24) + 1, FIND("~", SUBSTITUTE(Inputs24, "/", "~", 2)) - 1 - FIND("/", Inputs24))))

You cannot use weeks directly with this date, but you should be able to insert this formula into the appropriate Date cell and subtract 7 (days) from it, for example, for one week, etc.

For example:

=DATE(2000 + VALUE(RIGHT(Inputs24, 2)), VALUE(LEFT(Inputs24, FIND("/", Inputs24) - 1)), VALUE(MID(Inputs24, FIND("/", Inputs24) + 1, FIND("~", SUBSTITUTE(Inputs24, "/", "~", 2)) - 1 - FIND("/", Inputs24))))-7

Will this work for you?

Kelly

jjesmith

This is great. Thank you.