Thanks:  0
Likes:  0

# Thread: NUMBER TO TIME CONVERT

1. Thanks Derek, I'll try it!

Brian

2. Derek,

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

3. 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!

4. Aloha Yogi,

Yes it does work with the '.

Brian

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

6. Yogi,

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

Brian

7. 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

8. 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 ]

9. 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 ]

10. Yogi,

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

Brian

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•