convert number to time

[PixelPaul]

I want to convert a numerical entry into a time value, displayed as HH:MM:SS. For example:

User inputs 136, cell would display 1:36
User inputs 25, cell would display :25
User inputs 20348, cell would display 2:03:48

The displayed numbers need to be time format, they will be added and subtracted to other time values. I hope this makes sense.

[scottylad2]

With this formula it will include seconds, TEXT(H15,"00\:00\:00")+0 if you type 2500 in H15 you'll get 00:25:00 for 25 minutes. if you only want to type 2 or 3 numbers ie 25 = 25mins then lose the seconds counterTEXT(H15,"00\:00")+0. I can't make it work the way you want but i can imagine it can be written into an if statement

[Ron Coderre]

With the understanding that this numeric format won't actually convert the value to a time, it WILL display it as a time.

Please Login or Register  to view this content.

Apply that format to all of the "time" values.

You could then use this kind of formula to sum those "times" into an actual time value:

Please Login or Register  to view this content.

EDITED TO INCLUDE THIS EXAMPLE:

Please Login or Register  to view this content.

And the formula aboves sums those numbers into this time: 1:01:01


Is that something you can work with?

[scottylad2]

output with this TEXT(H15,"00\:00\:00")+0 <---25 00:00:25
output with this TEXT(H16,"00\:00")+0 <-----25 00:25:00

with this formula combined with len
25 00:25:00
20348 02:03:48
=IF(LEN(H16)<4,TEXT(H16,"00\:00")+0,TEXT(H16,"00\:00\:00"))+0
perhaps

[PixelPaul]

Thanks guys, I'll give these a try....as soon as I can figure out how to input the formulas. Very new at this!