timevalue over 24 hours

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I'm converting text info to time format using =TIMEVALUE
(D2), the destination cell is formatted with [h]:mm:ss.
The formula works fine as long as orig value is less than
24 hours. If over it returns wrong value: 29:56:33,
returns 5:56:33. I thought [h] resolved that issue. Can
someone help me solve this puzzle?

Thanks!!!
 
Hi
TIMEVALUE won't return values >24 hours. If you could post your source
data a different solution should be easily possible
 
Don't use TIMEVALUE, use

=--D2



--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Timevalue does only time, not date. So all multiples of 24 hours are
discarded when you use this function.

HTH. Best wishes Harald
 
The following is the column I need to convert to
[h]:mm:ss. Since a portion is in mm:ss I can't convert
the entire column using the same formula. I need the
results to be in hh:mm:ss with some values exceeding 24
hours. I thought I had the solution from the =TIMEVALUE
but wasn't aware of the 24 hour limit. argh. THANKS for
your help.

14:27
0:00
18:21:49
0:00
29:33:46
12:17:20
34:31:52
35:59:06
17:02:59
16:16:02
12:25:58
39:07:37
0:00
34:49:46
36:42:15
0:00
0:00
22:21:33
18:00:28
15:29:53
0:00
40:19:03
20:57:06
404:31:00
-----Original Message-----
Hi
TIMEVALUE won't return values >24 hours. If you could post your source
data a different solution should be easily possible

--
Regards
Frank Kabel
Frankfurt, Germany

Karen said:
I'm converting text info to time format using =TIMEVALUE
(D2), the destination cell is formatted with [h]:mm:ss.
The formula works fine as long as orig value is less than
24 hours. If over it returns wrong value: 29:56:33,
returns 5:56:33. I thought [h] resolved that issue. Can
someone help me solve this puzzle?

Thanks!!!
Click to expand...

.
Click to expand...
 
Hi
try Peo's suggestion:
=--A1

--
Regards
Frank Kabel
Frankfurt, Germany

Karen said:
The following is the column I need to convert to
[h]:mm:ss. Since a portion is in mm:ss I can't convert
the entire column using the same formula. I need the
results to be in hh:mm:ss with some values exceeding 24
hours. I thought I had the solution from the =TIMEVALUE
but wasn't aware of the 24 hour limit. argh. THANKS for
your help.

14:27
0:00
18:21:49
0:00
29:33:46
12:17:20
34:31:52
35:59:06
17:02:59
16:16:02
12:25:58
39:07:37
0:00
34:49:46
36:42:15
0:00
0:00
22:21:33
18:00:28
15:29:53
0:00
40:19:03
20:57:06
404:31:00
-----Original Message-----
Hi
TIMEVALUE won't return values >24 hours. If you could post your source
data a different solution should be easily possible

--
Regards
Frank Kabel
Frankfurt, Germany

Karen said:
I'm converting text info to time format using =TIMEVALUE
(D2), the destination cell is formatted with [h]:mm:ss.
The formula works fine as long as orig value is less than
24 hours. If over it returns wrong value: 29:56:33,
returns 5:56:33. I thought [h] resolved that issue. Can
someone help me solve this puzzle?

Thanks!!!
Click to expand...

.
Click to expand...
Click to expand...
 
It worked!!!! THANKS!!!
-----Original Message-----
Hi
try Peo's suggestion:
=--A1

--
Regards
Frank Kabel
Frankfurt, Germany

Karen said:
The following is the column I need to convert to
[h]:mm:ss. Since a portion is in mm:ss I can't convert
the entire column using the same formula. I need the
results to be in hh:mm:ss with some values exceeding 24
hours. I thought I had the solution from the =TIMEVALUE
but wasn't aware of the 24 hour limit. argh. THANKS for
your help.

14:27
0:00
18:21:49
0:00
29:33:46
12:17:20
34:31:52
35:59:06
17:02:59
16:16:02
12:25:58
39:07:37
0:00
34:49:46
36:42:15
0:00
0:00
22:21:33
18:00:28
15:29:53
0:00
40:19:03
20:57:06
404:31:00
-----Original Message-----
Hi
TIMEVALUE won't return values >24 hours. If you could post your source
data a different solution should be easily possible

--
Regards
Frank Kabel
Frankfurt, Germany

I'm converting text info to time format using =TIMEVALUE
(D2), the destination cell is formatted with [h]:mm:ss.
The formula works fine as long as orig value is less than
24 hours. If over it returns wrong value: 29:56:33,
returns 5:56:33. I thought [h] resolved that issue. Can
someone help me solve this puzzle?

Thanks!!!

.
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top