Converting result from an Index & Match formula from date to text

Andres Pimentel

Hello all,

I am trying to work on a formula to make it easier for me to work on Global Updates in Control Center easier in the future. I currenly have a formula that coverts the data from Date to Text instead of just a simple link to the (lets say) the Start Date column; I had to do this because of the Global differences in date formats.

So what I want to do is run an Index formula that will search for the specific task name and then display the Start date for that task in my cell where the link is. The problem i am running into is that the column where this formula sits is a Text column.This the formula as it currently is.

=INDEX([Project Milestones]:Predecessors, MATCH("Task name", [Project Milestones]:[Project Milestones], 0), 6)

If i change the text column to a date column then the formula works.

Thanks in advance.

Andrée Starå

Hi Andres,

This might help! (Just released)

https://community.smartsheet.com/announcement/apply-date-formats-column

Hello Community,



Teams that span multiple regions often encounter issues with date formatting. For example, while 05/03 is May 3rd in the US, it might stand for March 5th in other locales. As one of our top requested features, additional date formats for date columns is critical for the many teams that work with stakeholders across multiple locations, and it is now available to use.

You will now be able to:

• Adjust dates to your desired format at the cell or column level
• Update date formatting in grid view and Gantt view
• Display dates with the new formatting on reports and dashboards

To learn more about this release, see our help article. Then select the date format that works best for you!

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andres Pimentel

Hi Andree,

Thanks this is good to know however I still have the issue with the result going into a text format column.

Andrée Starå

Happy to help!

I'll get back to you if I think of something regarding the text format.

Best,

Andrée

Debbie Sawyer

Hello

I have had a play with your formula in a sheet and it appears that the column referenced by the number 6 at the end of your formula needs to be the same datatype as the cell that the formula is in.

So, if you have placed the formula in a Text/Number cell then the column referenced by the number 6 also needs to be Text/Number, my guess is that your column referenced by the number 6 is a Date datatype, as you said if you turn your cell containing the formula to a Date datatype then it works; but I guess from the original scenario you need it to be a Text/Number solution.

So can you convert your column 6 to a Text/Number type or create a helper column (column7) that concatenates the values from a date into text e.g. =DAY([Column6]@row) + "/" + MONTH([Column6]@row) + "/" + YEAR([Column6]@row)

Then refer to this new column 7 in your original formula?

=INDEX([Project Milestones]:Predecessors, MATCH("Task name", [Project Milestones]:[Project Milestones], 0), 7)

NB - ensure your indexed range ([Project Milestones]:Predecessors) includes the new helper row!

Hope this helps

Kind regards

Debbie

Paul Newcome

Generally speaking, you can convert most formula results to text by adding a + "" to the end of it. (plus double quotes).

 

Give this a whirl...

 

=INDEX([Project Milestones]:Predecessors, MATCH("Task name", [Project Milestones]:[Project Milestones], 0), 6) + ""

Debbie Sawyer

OOh - Thank you Paul - I didn't know that!

everyday is a learning day!!

Kind regards

Debbie

Paul Newcome

Debbie,

 

Sure thing. I use this a lot to place metrics in checkbox columns.

Andres Pimentel

Hi Debbie and Paul,

 

Thank you very much. Almost there. Debbie, I like the helper column idea if all else fails. Paul that seem to have worked except it is showing the time stamp as well. Now i need to confirm it is actually showing up as text when someone opens up the sheet that has different date settings then me before I perform a Global Update to this change.

Paul Newcome

I didn't realize you were using a system generated column. If all you want to show is the date and drop the timestamp, we just wrap your INDEX/MATCH in a DATEONLY function.

 

=DATEONLY(INDEX([Project Milestones]:Predecessors, MATCH("Task name", [Project Milestones]:[Project Milestones], 0), 6)) + ""

 

This will pull the date and the the plus double quotes will convert it to text.

Paul Newcome

To test whether it is text or not, you can simply put it in a text type column. If the data is in text format, it will look like the date, but if the data is in date format, it will throw an #INVALID COLUMN VALUE error.

Andres Pimentel

Thanks again Paul. I was playing around with that after your last post and there is something to complicate things further. After making your changes and changing the date format in my account I noticed why Smartsheet consultants had split it up further with this formula originally. In order for the intake sheet for Control center to display the data correctly I need to keep it looking like the US format in text.

IFERROR(MONTH(DATEONLY([Actual Finish]57)) + "-" + DAY(DATEONLY([Actual Finish]57)) + "-" + YEAR(DATEONLY([Actual Finish]57)), "")

This is how far I have gotten in trying to duplicate that effort. So I can now show the month alone but I am trying to see how to bring the day and year. I have been playing with the IF formula with no luck.

=MONTH(DATEONLY(INDEX([Project Milestones]:Predecessors, MATCH("Task Name Complete", [Project Milestones]:[Project Milestones], 0), 5))) + "-" + ""

Paul Newcome

You are actually on the right track, and you don't need an IF statement. We can also remove the +"" since you are already inserting text with + "-".

 

You have the month:

=MONTH(DATEONLY(INDEX([Project Milestones]:Predecessors, MATCH("Task Name Complete", [Project Milestones]:[Project Milestones], 0), 5))) + "-"

.

This will give you the day:

=DAY(DATEONLY(INDEX([Project Milestones]:Predecessors, MATCH("Task Name Complete", [Project Milestones]:[Project Milestones], 0), 5))) + "-"

.

And the year:

=YEAR(DATEONLY(INDEX([Project Milestones]:Predecessors, MATCH("Task Name Complete", [Project Milestones]:[Project Milestones], 0), 5)))

.

Now we just combine them into a string:

=MONTH(DATEONLY(INDEX([Project Milestones]:Predecessors, MATCH("Task Name Complete", [Project Milestones]:[Project Milestones], 0), 5))) + "-" + 

DAY(DATEONLY(INDEX([Project Milestones]:Predecessors, MATCH("Task Name Complete", [Project Milestones]:[Project Milestones], 0), 5))) + "-" + YEAR(DATEONLY(INDEX([Project Milestones]:Predecessors, MATCH("Task Name Complete", [Project Milestones]:[Project Milestones], 0), 5)))

Andres Pimentel

You are a rockstar Paul! Thanks. Now will just get it confirmed with Smartsheet that it won't break anything in Control Center before I pass it through a few hours of Global updates.

Paul Newcome

Happy to help!