Forum Discussion
TIMEVALUE function not working despite identical setup to one that does work
I have two spreadsheets set up in exactly the same way; same formulas, same formatting, everything identical. The first was produced last year; the second is a copy of the first made this year. In the old one, the TIMEVALUE function works perfectly. In the new one, it does not.
I am extracting a date and time from a text string. The initial text string (from an appointment booking site) shows as (for example): "Sep 10 2022, 3:45 PM" (without the quotation marks).
- My first column with formulas (call it column T) uses the SUBSTITUTE formula to remove the comma from the original text: =SUBSTITUTE(F3,",",""), where F is the column with the original text, and row 3 is the row we're looking at
- In the second column (column U), I trim the front end of the text in column T to remain with only the time. The formula is =RIGHT(T3,8). I am then left with a text showing " 3:45 PM". There is a space in front of the 3, because the 10:00, 11:00 and 12:00 times require the extra digit.
- The third column (column V) I had to update from the old spreadsheet to the new one. The old one trimmed column T to remain with only the date; the formula here was =LEFT(T3,6), so the text would show as "Sep 10". The new one flips the date and month, so the formula is =MID(T3,5,2)&" "&LEFT(T3,3), so the text shows as "10 Sep". The next point explains why I had to change this.
- The fourth column (Date value - column W) converts the date text to a numerical date. Both spreadsheets use the formula =IFERROR(DATEVALUE(V3),""). In the old one, this worked fine, and it would convert the text-based date to a numerical date of 10-Sep-2021. In the new one one, it was interpreting Sep 10 as "September 2010," rather than as 10_Sep-2022. This is the first mystery I cannot solve - why are these reading exactly the same text differently? Again, I emphasize that all the formulas and formatting is identical. I was able to work around this by flipping the date and month in column V, as mentioned above.
- The last column (column X) converts the text-based AM/PM time to a numerical 24-hour time. The formula here is =IFERROR(TIMEVALUE(U3),""). In the old spreadsheet this worked perfectly. In the new one, it cannot read the text and produces an error. The setup is exactly the same and the formatting of each cell is exactly the same. What might be causing this? Is there another setting somewhere which might have changed?
Thanks,
For the date, use
=LET( pos, FIND(",", F3), dat, LEFT(F3, pos-1), m, LEFT(dat, 3), y, RIGHT(dat, 4), d, MID(dat, 5, LEN(dat)-9), DATEVALUE(d&"-"&m&"-"&y))Format the cell with the formula as a date.
For the time:
=LET( pos, FIND(",", F3), tim, MID(F3, pos+2, 100), TIMEVALUE(tim))Format the cell with this formula as time.
HansVogelaar Thanks! That worked for the date - and allowed me to remove some of my 'helper' columns - but the time still comes back with an error.
I've tried deciphering your formula so I could tinker with it. I tried changing 100 characters after pos+2 in the MID command to 5 characters. (Incidentally - is there a reason for the space before the 100?). WIth a 5-character limit, it correctly reflects the 12-hour time (no errors), but since 5 characters chops off the AM/PM at the end of the text, it reads PM times as AM. I would have thought specifying 8 characters (2 digits hour, 1 colon, 2 digits minute, 1 space, 2 characters for AM or PM) would solve this, but it also returns an error.
This takes me back to the initial problem, which is that the TIMEVALUE function does not seem to be able to recognize text which contains AM/PM as a time. In my old spreadsheet, TIMEVALUE worked in this regard. In the new one, it works only for the HH:MM, but once the AM/PM is included, it fails.
Try this. The spaces after commas in the formula are just to make it easier to read, they are not essential, just like the line breaks.
=LET( pos, FIND(",", F3), tim, MID(F3, pos+1, 100), tima, SUBSTITUTE(SUBSTITUTE(tim, " ", ""), CHAR(160), ""), timb, TIMEVALUE(LEFT(tima, LEN(tima)-2)), extra, IF(RIGHT(tima, 2)="PM", TIME(12, 0, 0), 0), timb+extra)The 100 is just an arbitrary large value, it could have been 10 or 255.
GJG_MS Same Excel versions? Same computers?
- One was created last year, the new one is a save-as from the old one, with new data copied in, but formulas untouched. Pretty sure the version of Excel is the same. Might have been created on different laptops.
