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

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?

Answers

  • Kelly Moore
    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

  • 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

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

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

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

  • This is great. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!