NUMBER TO TIME CONVERT

[CJ]

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

[BabyTiger]

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.

[Aladin Akyurek]

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.

[CJ]

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

[Yogi Anand]

On 2002-03-21 13:25, Aladin Akyurek wrote:

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.

[Yogi Anand]

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

[Aladin Akyurek]

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 ]

[BabyTiger]

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.

[Yogi Anand]

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 ]

[CJ]

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