Delimit Date/time in a match/index function

murphyspccms

Hi all,

I'm using a match/index function currently to pull over data from another sheet.

Currently I'm using this:

=IFERROR(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), "")

The sheet I'm referencing in the function has time and date in the same column, so the above formula pulls over data that looks like this:2023-06-17T12:30:53-04:00

I'm hoping toonly pull over the date, not the time. I suspect the MID functionwould be appropriate to add into my formula, but I've never actually used it. Asking for help to see if this possible? if so, how would I add that into my current formula.

Apologies if I'm missing any necessary context.

Thank you!

Paul Newcome

The DATEONLY function will not work because it was designed to pull the date out of something that is already stored as a date or date/time stamp on the back-end. The above data is a text string.

Try this instead:

=IFERROR(DATE(VALUE(LEFT(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 4)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 6, 2)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 9, 2))), "")

Paul Newcome

What happens when you remove the VALUE from it?

=SUBSTITUTE(SUBSTITUTE([Column name]@row, "$", ""), "USD", "")

Carson Penticuff

Will the date always be the first 10 characters? If so, you can use LEFT and specify the first 10 digits.

=IFERROR(LEFT(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 10), "")

murphyspccms

@Carson Penticuffthat worked perfectly for my initial question, but now I'm wondering how to get what's pulled into a date format.

I have a function in a different column to calculate the date difference between 2 columns. I attached a screen shot, the 2nd row shows your response working as it should be.

Gia Thinh

Hi murphyspccms ,

DATEONLY Function | Smartsheet Learning Center

I suggest using DATEONLY function to extract the date portion of a date/time value. You can add DATEONLY to your current formula as below:

=IFERROR(DATEONLY(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0))), "")

Hope that helps.

murphyspccms

@Gia Thinhis it possible to combine your function with@Carson Penticuff's function:

=IFERROR(LEFT(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 10), "")

I ask, because I didn't have luck with yours, the cell was blank. I'm wondering if it's because of the extra time info in the cell with the date.

Paul Newcome

The DATEONLY function will not work because it was designed to pull the date out of something that is already stored as a date or date/time stamp on the back-end. The above data is a text string.

Try this instead:

=IFERROR(DATE(VALUE(LEFT(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 4)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 6, 2)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 9, 2))), "")

murphyspccms

@Paul Newcomeamazing. This works. Thank you!

Paul Newcome

Happy to help.️

murphyspccms

hey@Paul Newcome, I'd like to use the formula you gave me but adjust it for another column that contains USD, but I'd like to only pull over the numbers that way I can use the column for basic math functions such as AVG.

I've been researching to see if there's a find and replace formula that'll automatically replace USD with blanks, but I don't think that's a viable option.

The problem that I anticipate with the formula that you made me, is the string of texts from the left aren't always going to be the same, the only common issue will be the USD on the end. Is there a "RIGHT" function?

I'm referring to this BTW from earlier:

=IFERROR(DATE(VALUE(LEFT(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 4)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 6, 2)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 9, 2))), "")

What are your thoughts? thank you so much for your help

Paul Newcome

You would have to convert them into a numeric value on the source sheet. There is a RIGHT function, but we don't need it here. The below should work for stripping the $ and "USD" out and leave you with a numeric value.

=VALUE(SUBSTITUTE(SUBSTITUTE([Column name]@row, "$", ""), "USD", ""))

murphyspccms

@Paul NewcomeI just tried this, but I'm getting an error. I have no idea how to troubleshoot this. Does the column properties need to be changed?

Paul Newcome

What happens when you remove the VALUE from it?

=SUBSTITUTE(SUBSTITUTE([Column name]@row, "$", ""), "USD", "")

murphyspccms

@Paul NewcomeI think that's it. Thank you!!