Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 276

# How to Convert Number to Time value - Excel 2007

Hello experts,

I have a table in excel containing time but written as number (not time values)

For example 600 instead of 06:00

I need your help in developing a user function that would convert 600 to 06:00.

Please see attched sample sheet.

Thank you     Convert-number-to-Time.xlsx
0
Mehawitchi
3 Solutions

Commented:
1. select the entire column
2. right click and select format cells
3. select time.
0

IT super heroCommented:
A1:600

B1:
=IF(LEN(A1)=3;"0"&LEFT(A1;1)&":"&RIGHT(A1;2);LEFT(A1)&":"&RIGHT(A1;2))
0

Author Commented:
Hi viralypatel
Thanks for the suggested solution, but I've tried this already and didn't work. It gave me wrong dates and time.

Have you tried it yourself? Did it work?
0

Commented:
I don't normally go near date/time questions, but this seems to work for your sample:

=TIME(C2/100,RIGHT(C2,2),0)
0

Commented:
Took me so long I didn't see all those posts!
0

Commented:
Mehawitchi,

This is a cell formula. If you need a VBA formula it should be easy to convert.

=TIME(MID(C2,1,CHOOSE(LEN(C2),1,1,1,2)),MID(C2,CHOOSE(LEN(C2),3,3,2,3), 2),0)

Sincerely,
T-Bone
0

Commented:
You can use this formula in another column, see attached

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

format as hh:mm

regards, barry
27343557.xlsx
0

Author Commented:
Hey, I'm overwhelmed.

All solutions worked but my preference goes to StephenJR because in one step I have a time value
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.