0

I have some information that I copy/paste from a text file. These come across in such a fashion that I have to do a "Text to Column", which does leave the time neatly in their own column in the format of H:MM. I can use the formula =C2*60*24 (because C2 is where the first time I need to convert is placed, followed by the next time in C3, etc.). That formula works except for values under 1 hour. For example, if I have a cell that says :22 (for 22 minutes), the formula cell results are "#VALUE!"??? Anyone have a solution or a fix to try?

Rich
  • 111
  • 2
  • 13
  • That's interesting. It's because :22 is not a valid time. There NEEDS to be an hour there in order for it to resolve. You may have to come up with some other formula that uses 22/60ths for its base. – durbnpoisn Jan 05 '16 at 19:02
  • I'm not sure I understand your solution completely? How would I do that b/c every type of math I try on that cell just gives me a "#VALUE!" as a result? So how would I do it as a base of 22/60? And wouldn't that only work on times of 22 minutes? – Rich Jan 05 '16 at 19:04
  • This worked to get :22 as time, then I could use the =C2*60*24 to get the minutes: =IF(LEFT(C2)=":",CONCATENATE(0,C2), C2) – Rich Jan 05 '16 at 19:15

1 Answers1

0

Can you prefix with a zero and use the TIMEVALUE function?

=TIMEVALUE(0&C2)*60*24

Optionally, only prefix with the zero if there is a leading colon.

=TIMEVALUE(IF(LEFT(C2, 1)=":", 0, "")&C2)*60*24
  • Thank you! I had just discovered someone saying to use a "LEFT" function. :) Though the solution you have didn't work for me. This worked to get :22 as time, then I could use the =C2*60*24 to get the mintues: =IF(LEFT(C2)=":",CONCATENATE(0,C2), C2) – Rich Jan 05 '16 at 19:18