NUMBER TO TIME CONVERT

CJ

Board Regular
Joined
Feb 22, 2002
Messages
77
Hi guys, A nice simple one!!!!

I have imported some data into a spreadsheet
but the time has come across as a number
ie, 1630 instead of 16:30 (to many to do manually - 500+)

Can anyone advise me on a formula or a custom format to insert a colon in the number two places in from the right

CHRIS
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi,

If you are looking for an Excel formula, then you mgiht want to try the following:

=TIME(LEFT(E15,2),RIGHT(E15,2),0)

If you are looking for an macro formula/code, then the following might help

Range("A1").Value = TimeSerial(Left(Range("E15").Value, 2), Right(Range("E15").Value, 2), 0)

Hope it helps.
 
Upvote 0
On 2002-03-21 13:08, CJ wrote:
Hi guys, A nice simple one!!!!

I have imported some data into a spreadsheet
but the time has come across as a number
ie, 1630 instead of 16:30 (to many to do manually - 500+)

Can anyone advise me on a formula or a custom format to insert a colon in the number two places in from the right

CHRIS

In B1 enter and copy down:

=(LEFT(REPT("0",4-LEN(A1))&A1,2)&":"&RIGHT(A1,2))+0

where A1 houses the first target value.
 
Upvote 0
Hi Baby Tiger,

Brillant that's just what I was looking for,
one other little thing I would like the time to show in a 24 hr clock format could this be done in the formula,I know that I could reformat the the cells to a [h]:mm format but would like if poss to do this in the formula!!!

CHRIS
 
Upvote 0
On 2002-03-21 13:25, Aladin Akyurek wrote:
On 2002-03-21 13:08, CJ wrote:
Hi guys, A nice simple one!!!!

I have imported some data into a spreadsheet
but the time has come across as a number
ie, 1630 instead of 16:30 (to many to do manually - 500+)

Hi CJ:
as long as both the hours and the minutes are in two digits, you can use the following formula to convert 1650 to 16:45

=LEFT(C31,2)&":"&RIGHT(C31,2)
in this case 1650 is housed in cell C31


Can anyone advise me on a formula or a custom format to insert a colon in the number two places in from the right

CHRIS

In B1 enter and copy down:

=(LEFT(REPT("0",4-LEN(A1))&A1,2)&":"&RIGHT(A1,2))+0

where A1 houses the first target value.
 
Upvote 0
sorry guys, above post got all messed up

Chris: If both the hours and the minutes are in two digits each, and say 1650 is in cell C31, then to convert 1650 into 16:50, use the following formula:

=left(c31,2)&":"&right(c31,2)

HTH
 
Upvote 0
On 2002-03-21 13:44, Yogi Anand wrote:
sorry guys, above post got all messed up

Chris: If both the hours and the minutes are in two digits each, and say 1650 is in cell C31, then to convert 1650 into 16:50, use the following formula:

=left(c31,2)&":"&right(c31,2)

HTH

Try the formula I proposed: It should also work for, say:

445
20
00

Aladin
This message was edited by Aladin Akyurek on 2002-03-21 14:15
 
Upvote 0
Hi CJ,

To format the time to 24hrs format, in Excel:

=text(time(),"hh:mm")

In macro:

=format(time(),"hh:mm")

Then it should be 24 hrs.
 
Upvote 0
On 2002-03-21 14:06, BabyTiger wrote:
Hi CJ,

To format the time to 24hrs format, in Excel:

=text(time(),"hh:mm")

In macro:

=format(time(),"hh:mm")

Then it should be 24 hrs.

Just to elaborate what BabyTiger said about the formula ...
if the time was shown as 1650 as in your example, you will have to parse 1650 into hours (16), minutes (50), and seconds (0) before you can use this in the TIME function ... so Baby Tiger's formula expanded will look like (if 1650 were in cell A1)...

=TEXT(TIME(left(A1,2),right(A1,2),0),"hh:mm")


_________________
Yogi Anand
Edit: Deleted reference to inactive web site from signature line
This message was edited by Yogi Anand on 2003-01-19 18:41
 
Upvote 0
Thanks Yogi,

I was just going to reply to TB's post to ask where: =text(time(),"hh:mm") went in the formula, then I saw your reply. I now understand what TB meant.

=text(time(TB ORIG FORMULA),"hh:mm")

I'm learning slowly!!

Thanks to everyone who replied to my post

You have all saved me alot of typing

CHRIS
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top