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(claimNum@row, {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 to only pull over the date, not the time. I suspect the MID function would 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(claimNum@row, {claim num}, 0)), 4)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH(claimNum@row, {claim num}, 0)), 6, 2)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH(claimNum@row, {claim num}, 0)), 9, 2))), "")

Paul Newcome

What happens when you remove the VALUE from it?

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

Paul Newcome

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

=VALUE(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(claimNum@row, {claim num}, 0)), 10), "")

murphyspccms

@Carson Penticuff that 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(claimNum@row, {claim num}, 0))), "")

Hope that helps.

murphyspccms

@Gia Thinh is 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(claimNum@row, {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(claimNum@row, {claim num}, 0)), 4)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH(claimNum@row, {claim num}, 0)), 6, 2)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH(claimNum@row, {claim num}, 0)), 9, 2))), "")

murphyspccms

@Paul Newcome amazing. 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(claimNum@row, {claim num}, 0)), 4)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH(claimNum@row, {claim num}, 0)), 6, 2)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH(claimNum@row, {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 Newcome I 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 Newcome I think that's it. Thank you!!

Paul Newcome

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

murphyspccms

@Paul Newcome No luck on this either.

murphyspccms

@Paul Newcome here's a sanitized published version https://app.smartsheet.com/b/publish?EQBCT=99ecb6687b5646f897add9029e952525