Sep 16 2022 02:25 AM
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).
Thanks,
Sep 16 2022 03:21 AM
@GJG_MS Same Excel versions? Same computers?
Sep 16 2022 03:40 AM
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.
Sep 16 2022 04:28 AM
@Hans Vogelaar 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.
Sep 16 2022 04:36 AM
Sep 16 2022 04:46 AM
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.
Sep 16 2022 05:06 AM
Sep 16 2022 05:19 AM
Sep 16 2022 05:24 AM
Ah yes, the silly AM/PM system...
New version:
=LET(
pos, FIND(",", F3),
tim, MID(F3, pos+1, 100),
tima, SUBSTITUTE(SUBSTITUTE(tim, " ", ""), CHAR(160), ""),
timb, TIMEVALUE(LEFT(tima, LEN(tima)-2)),
timc, MOD(timb, TIME(12, 0, 0)),
extra, IF(RIGHT(tima, 2)="PM", TIME(12, 0, 0), 0),
timc+extra)