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

Uploaded May 16, 202336.33 KB

SS 1.PNG

Uploaded May 16, 20231.7 KB

, 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.

Converting a text (MONTH YEAR) into a date

Adam1234

Sep 14, 2022

Hello,

I am trying to convert a text box that shows the month and year only in a column. So if the cell with the text of “October 2022” is shown, I want a different column to produce “10/01/22”

I would also want to use this date column for automations, so I am curious to know if column formulas could be used as triggers for automations.

To better explain based on the previous example, if October 1st 2022 is the trigger for the automation would it run?

Any insight helps! Thank you!

---

View Post

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.