Is there a method by which you can display a value easily read as a
time value (hh:mm:ss) where hour value can be greater than 24?

I have values that look like time - but provided as text - and I need
them sortable. The hours, however, are not forced to 2 characters like
the minute and second places are. I thought about adding a custom LPad
Function and padding the string out to 8 places with leading 0s... but
would rather stay within the excel framework if possible.

Examples:
0:06:18
1:44:46
2:15:52
5:03:22
17:40:56
38:05:59

Would currently sort like:
0:06:18
1:44:46
17:40:56
2:15:52
38:05:59
5:03:22

I was going to just convert it to a time value - then I found out
values greater than 24 were possible.

Thanks in advance
Ray

I admit I'm not sure if you wanted to sort the first way or the second way.

If the first way,

Format Cells as Time and select the option that shows 37:30:55



"Ray" wrote:

Is there a method by which you can display a value easily read as a
time value (hh:mm:ss) where hour value can be greater than 24?

I have values that look like time - but provided as text - and I need
them sortable. The hours, however, are not forced to 2 characters like
the minute and second places are. I thought about adding a custom LPad
Function and padding the string out to 8 places with leading 0s... but
would rather stay within the excel framework if possible.

Examples:
0:06:18
1:44:46
2:15:52
5:03:22
17:40:56
38:05:59

Would currently sort like:
0:06:18
1:44:46
17:40:56
2:15:52
38:05:59
5:03:22

I was going to just convert it to a time value - then I found out
values greater than 24 were possible.

Thanks in advance
Ray

If you covert it to a time, and format as [h]:mm:ss it will handle times
greater than 24 hours.
--
David Biddulph

"Ray" wrote in message
...
Is there a method by which you can display a value easily read as a
time value (hh:mm:ss) where hour value can be greater than 24?

I have values that look like time - but provided as text - and I need
them sortable. The hours, however, are not forced to 2 characters like
the minute and second places are. I thought about adding a custom LPad
Function and padding the string out to 8 places with leading 0s... but
would rather stay within the excel framework if possible.

Examples:
0:06:18
1:44:46
2:15:52
5:03:22
17:40:56
38:05:59

Would currently sort like:
0:06:18
1:44:46
17:40:56
2:15:52
38:05:59
5:03:22

I was going to just convert it to a time value - then I found out
values greater than 24 were possible.

Thanks in advance
Ray