Delimit Date/time in a match/index function

murphyspccms · 06/23/23

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 · 06/26/23

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 · 06/27/23

What happens when you remove the VALUE from it?

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

Paul Newcome · 06/28/23

Total facepalm moment. Sorry. We need to also remove the space from before USD.

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

Carson Penticuff · 06/23/23

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 · 06/23/23

@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 · 06/26/23

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 · 06/26/23

@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 · 06/26/23

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 · 06/26/23

@Paul Newcomeamazing. This works. Thank you!

Paul Newcome · 06/26/23

Happy to help.️

murphyspccms · 06/27/23

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 · 06/27/23

你必须将其转换为一个数字价值e 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 · 06/27/23

@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 · 06/27/23

What happens when you remove the VALUE from it?

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

murphyspccms · 06/27/23

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

Paul Newcome · 06/28/23

That's not quite it. That gives us a text string, and it won't be able to be added, averaged, etc.. We need to figure out what exactly is making the VALUE function error out. I am wondering if it is the ".", so give this a try:

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