Forum Discussion
TIMEVALUE function not working despite identical setup to one that does work
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.
- That worked, except since you're adding 12 hours to anything with a PM after it, 12:30 PM reads as 00:30 rather than 12:30. The inverse would be true for a 12:30 AM time, which should read as 00:30, but will end up showing as 12:30.
I had also tried something similar - adding 12 hours if there is PM in the text - using an IF command through one of my helper columns. I had the same problem and couldn't figure out a way around it. I had started trying to construct a complex IF-AND combination where it searches for everything that has 12: in it, but got exasperated. That's when I came here.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)- Never mind - I fixed it! Just had to add an AND within the IF to exclude any text starting in "12".
Note the change in the second last line:
=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(AND(RIGHT(tima, 2)="PM",LEFT(tima,2)<>"12"), TIME(12, 0, 0), 0),
timb+extra)