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

 

[Brian from Maui]

CJ

Learned this from this board. Only you won't be able to add/subtract time.

Format/Cells/Custom
00:00

 

[CJ]

Thank Brain

Tried what you mentioned but it won't accept it as a format!!!

CHRIS

 

[Brian from Maui]

Chris,

I just tried it, it works. I'm using Excel 97. Highlight the cells, then format..etc.

Brian

 

[Brian from Maui]

CJ

Should have only one back slash

00:00

Brian

 

[BabyTiger]

Alternatively, using Brian's method, but instead of "00:00", use "hh:mm"

 

[Yogi Anand]

On 2002-03-21 17:30, BabyTiger wrote:
Alternatively, using Brian's method, but instead of "00:00", use "hh:mm"

Hi Baby Tiger:
I was trying to follow what you meant, but I am not getting what tou meant. Brian's FORMAT|CELLS|NUMBER..."Custom 00:00" sans quotes works for me!

 

[Brian from Maui]

Using the format I gave, I'm trying to subtract the time.

11:59-13:00=61Mins.
Answer I get is 1:41
Anyone have any ideas?

Mahalo...Brian

 

[Derek]

Hi Brian
You could use an if statement that goes along the lines if A>=B then A-B else B-A.
regards
Derek

 

[Brian from Maui]

Derek,

In my situation, A1 will always be less than B1 as I'm using a 24 hour format unless we past midnight, but never so far. I got his custom format because I didn't want to type 12:00 but 1200 with the colon inserted by the custom format. But I can't get it to subtract properly. i.e. 11:59 and 13:00 to :61.

 

[Derek]

Brian
That's because the format you are using gives the appearance of time but the colon actually acts as a decimal point, so you are subtracting decimals not time.
I had a similar problem where I needed to enter 4 digit time in a time calculator.
I overcame it using hidden columns that were formatted [h]:mm
Try this:
Custom Format A1:B1 as 0000
Custom Format C1:E1 as [h]:mm
in A1 type 1159
in B1 type 1300
in C1 put formula =LEFT(A1,2)&":"&RIGHT(A1,2)
In D1 put formula =LEFT(B1,2)&":"&RIGHT(B1,2)
In E1 put formula =D1-C1
You can then hide columns C:D
Since time format won't allow negative time, you may have to revert to using the if function to take the smaller time from the larger, if this is an issue.

hope this helps
regards
Derek