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]

Thanks Derek, I'll try it!

Brian

 

[Brian from Maui]

Derek,

When I enter 830, the formula converts to 83:30. Should be 8:30. Tried different formatting but still same results.

 

[Yogi Anand]

On 2002-03-22 12:42, Brian from Maui wrote:
Derek,

When I enter 830, the formula converts to 83:30. Should be 8:30. Tried different formatting but still same results.

Hi Brian:
You are getting 83 ... this is the result of extracting two leftmost characters from the string 830 and not 0830. For your formula to work right you have to make sure that 0830 is forced in as text, otherwise it will revert to 830 -- you may force it to enter as '0830 by using an apostrophe as the beginning character. The rest of the stuff work right as discussed in the rest of the thread.
HTH
Please post back if it works for you ... otherwise explain it a little further and we will take it from there!

 

[Brian from Maui]

Aloha Yogi,

Yes it does work with the '.
Mahalo for your time

Brian

 

[Yogi Anand]

Good Job Brian!
Now you can take a coffee break ... or in Maui may be a coconut milk break!

 

[Brian from Maui]

Yogi,

One more question (maybe), can the formula be written with the ' already in the formula?

Brian

 

[Yogi Anand]

On 2002-03-23 11:15, Brian from Maui wrote:
Yogi,

One more question (maybe), can the formula be written with the ' already in the formula?

Brian

Hi Brian:
If you don't want to bother with keying the apostrophe in, before you put the single digit hours like 0830, you must format the cells with FORMAT|CELLS|NUMBER|CUSTOM|@
This forces the cell to be preformatted as text and will retain the 0 in 0830 and will not strip it.
HTH

 

[Brian from Maui]

Yogi,

Tried custom @00:00 that doesn't work
Tried custom '00:00 that works, but only adds the ' to the numbers and still results in 83:30

Would this work or something like this.

A macro to insert the : automatically in columns A and B
Then use the formulas posted in columns C, D, and E?

Brian
This message was edited by Brian from Maui on 2002-03-23 12:02

 

[Yogi Anand]

Hi Brian:
Let us say you have two times 0830 and 0600 (notice that these have been entered without the colons). Let us say 0830 will go in cell A2, 0600 will go in cell B2; and C2 will house the time difference between A2 and B2.
If you want to use the CUSTOM @ format as I suggested. Custom Format the cells A2 and B2 only with @ symbol -- do not combine it with 00:00 format.
Now with the cells A2 and B2 custom formatted, enter your data in 0830 in A2, and 0600 in B2 (notice no need for apostrophes if the cells are CUstom @ formatted).
Now in cell C2, write your formula as ...

=TIME(LEFT(A2,2),RIGHT(A2,2),0)-TIME(LEFT(B2,2),RIGHT(B2,2),0)

note in this case we don't have to use the 00:0 format at all

make sure you format the cell C2 as CUSTOM|[h]:mm:ss to see the result as 2:30:00

or format the cell C2 as GENERAL (or NUMBER, say with 2 decimal places), multiply the result in C2 by 24 to get the result in decimal representation as 2.5 (hours)
ALOHA!

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

 

[Brian from Maui]

Yogi,

It worked!!!!
I just flip flopped b-a part.
Mahalo for the time and effort

Brian