Time Conversion - Variable Number of Digits

Hi,

I'm stumped trying to create a Time in HH;MM format from a cell that has a variable number of digits in it. Excel doesn't see the values as time. I.e. I have a cell that has 6 digits, "230100", I need to convert that to "23:01", and drop the 00's at the end. It's tricky because when I hit midnight the cell will contain 1 digit, "0", which needs to be "00:00". Or I may have a cell that has 3 digits, "800", which would be "00:08". Or another example is 4 digits, "1700", needs to be "00:17".

I've attached a sample of data. I'm trying to input my data in Column I and Data is in Column C.

Variable Time Convert Calc Sample.xlsx

Any assistance is MUCH appreciated!!

EDIT: Please feel free to ask any questions if I wasn't clear.

Try this formula in I2

=TEXT(C2,"00\:00\:00")+0

format as hh:mm and copy down column

Originally Posted by ExcelQuestFL

......So you add 0 at the end to convert it back to a numerical format right?...

That's correct, any mathematical operation that doesn't change the value will work, e.g. *1 would work or this is a popular way

=--TEXT(C2,"00\:00\:00")

I'm trying to use this same formula differently and can't seem to get it to work. This time I'm attempting to output the hour from a general number (instead of seconds like the original post)

I have "13" in cell B3 and need it to read 13:00. I guess I didn't understand the formula because when I try =TEXT(B3,":00")+0, it returns #value. What am I doing wrong?

EDIT: I know what I'm doing wrong. The 13 is already number. I just need to figure out how to convert it to 13:00. How do I do that?

Try dividing by 24, i.e.

=B3/24

format result cell as [h]:mm

Excel's default "unit" is a day so it interprets 13 as 13 days (in terms of time) so if you actually want it to be 13 hours obviously that's a 24th of the original amount so you divide by 24. That will work for any number

[by the same logic is the 13 was actually 13 minutes you'd divide by 24*60 = 1440]