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
  • Last Modified:

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
Asked:
Mehawitchi
3 Solutions
 
viralypatelCommented:
1. select the entire column
2. right click and select format cells
3. select time.
0
 
x-menIT super heroCommented:
A1:600

B1:
=IF(LEN(A1)=3;"0"&LEFT(A1;1)&":"&RIGHT(A1;2);LEFT(A1)&":"&RIGHT(A1;2))
0
 
MehawitchiAuthor 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
StephenJRCommented:
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
 
StephenJRCommented:
Took me so long I didn't see all those posts!
0
 
sanofi-aventisCommented:
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
 
barry houdiniCommented:
You can use this formula in another column, see attached

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

format as hh:mm

regards, barry
27343557.xlsx
0
 
MehawitchiAuthor 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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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