Converting a Number to A Time thread68-973646 1

[kcoleman26]

EXECEL 2003
In the mentioned thread if gave the formula
=0+(LEFT(Q3,2)&":"&RIGHT(Q3,2))

to convert 1440 to 2:40 PM

The problem I am having with this formula is when I try to covert 920 to 9:20 AM for some reason it goes to 8:20 AM
some other numbers it goes to are strange as well

726 12:26 AM
728 12:28 AM
803 8:03 AM
804 8:04 AM
827 10:27 AM
827 10:27 AM
827 10:27 AM
828 10:28 AM
832 11:32 AM
832 11:32 AM
841 12:41 PM
845 12:45 PM
846 12:46 PM
902 6:02 PM
905 6:05 PM
905 6:05 PM
906 6:06 PM
948 10:48 PM
951 11:51 PM

I don't understand the formula enough to make an adjustment the ones that are messed up are the times with 3 numbers

Thanks,
KERRY

 

[AnotherHiggins]

Here's how the formula works:

[tab]"0+"
This just forces whatever comes next to be a number instead of a text string. See Excel's help file for more info on the difference.

[tab](LEFT(Q3,2)&":"&RIGHT(Q3,2))
Builds a text string

[tab]0+(LEFT(Q3,2)
Looks at the left two characters in the cell. This is where you're having trouble. The formula assumes that there will always be [highlight]4[/highlight] digits. So 920 would have to be 0920.

But you only have three digits. So the formula is looking at the left two digits and coming up with 92. Because Excel defaults to only showing the number of hours less than 24, it shows 8. (38 * 24 = 912, which is 8 less than 920.)

See faq68-5827 for more information on how Excel deals with dates and times.

[tab]&":"
Adds a colon to the string

[tab]&RIGHT(Q3,2))
Adds the right two characters to the string

- -

Before proceeding with possible solutions for your case, how are you determining whether a time is AM or PM? What would 2:20 PM look like in your data, 1420 or 220?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[kcoleman26]

2:20 PM would look like 1420

 

[AnotherHiggins]

In that case, try this:

[tab][COLOR=blue white]=Value(LEFT(Q3, Len(Q3) - 2) & ":" & RIGHT(Q3, 2))[/color]

Note: I just cahnged 0+ to Value as a personal preference.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[TonyJollans]

From time to time, I look at these things and I wonder ..

[blue][tt]=TEXT(TEXT(Q3,"0"":""00"),"hh:mm AM/PM")[/tt][/blue]

or this ..

[blue][tt]=MOD(TEXT(Q3,"0"":""00"),0.5)[/tt][/blue] formatted as Time

OK - perhaps I should stick to Word

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

First and foremost, read and understand the FAQ that John posted.

Convert 1440 to 2:40 PM

1440 is not to be interpreted as a number. What does 1440 really mean?

14 HOURS and 40 MINUTES into the day

Since Time is really a number like right now (18:42) is 0.779166667, you must convert HOURS and MINUTES to TIME. So is 1440 is in A1...
[tt]
=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}