Solved

How to Convert Number to Time value - Excel 2007

Posted on 2011-09-27
8
265 Views
Last Modified: 2012-05-12
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
Comment
Question by:Mehawitchi
8 Comments
 
LVL 12

Expert Comment

by:viralypatel
ID: 36709950
1. select the entire column
2. right click and select format cells
3. select time.
0
 
LVL 18

Expert Comment

by:x-men
ID: 36709983
A1:600

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

Author Comment

by:Mehawitchi
ID: 36709986
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 24

Accepted Solution

by:
StephenJR earned 250 total points
ID: 36709998
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
 
LVL 24

Expert Comment

by:StephenJR
ID: 36710001
Took me so long I didn't see all those posts!
0
 
LVL 2

Assisted Solution

by:sanofi-aventis
sanofi-aventis earned 150 total points
ID: 36710016
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
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 100 total points
ID: 36710027
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 Comment

by:Mehawitchi
ID: 36710125
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question