Prev: Explain INDEX MATCH
Next: Auto fill
From: CatKween on 6 Apr 2010 14:02
I am trying to merge to time cells, (both set to English (United States),
Time Type 13:30) both cells display correctly where I entered them (9:00 and
9:45) but when I merge the cell contents using the function =A4&" - "&B4 I
get the result 0.375 - 0.406 instead of 9:00 - 9:45 as expected. The
destination cell has the same time settings as the two original cells.

How do I stop the times converting to decimals and get them to display the
way I want them too?
From: Gord Dibben on 6 Apr 2010 14:25
=TEXT(A4,"hh:mm") & " - " & TEXT(B4,"hh:mm")


Gord Dibben MS Excel MVP



On Tue, 6 Apr 2010 11:02:20 -0700, CatKween
<CatKween(a)discussions.microsoft.com> wrote:

>I am trying to merge to time cells, (both set to English (United States),
>Time Type 13:30) both cells display correctly where I entered them (9:00 and
>9:45) but when I merge the cell contents using the function =A4&" - "&B4 I
>get the result 0.375 - 0.406 instead of 9:00 - 9:45 as expected. The
>destination cell has the same time settings as the two original cells.
>
>How do I stop the times converting to decimals and get them to display the
>way I want them too?

From: Gary Brown on 6 Apr 2010 14:54
=TEXT(A4,"hh:mm") & " - " & TEXT(B4,"hh:mm")
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"CatKween" wrote:

> I am trying to merge to time cells, (both set to English (United States),
> Time Type 13:30) both cells display correctly where I entered them (9:00 and
> 9:45) but when I merge the cell contents using the function =A4&" - "&B4 I
> get the result 0.375 - 0.406 instead of 9:00 - 9:45 as expected. The
> destination cell has the same time settings as the two original cells.
>
> How do I stop the times converting to decimals and get them to display the
> way I want them too?
From: Bernard Liengme on 6 Apr 2010 14:56
Gordon has given you the correct answer (as always).
Here is the reason for it:
Excel stores dates as serial numbers and times as factions of a 24-hr day
(is there any other type <gr>?)
So A1&"-"&B1 will display: number "-" number rather than time "-" time
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"CatKween" <CatKween(a)discussions.microsoft.com> wrote in message
news:81E1FDFC-B2EE-498D-9818-0A831F468DB8(a)microsoft.com...
> I am trying to merge to time cells, (both set to English (United States),
> Time Type 13:30) both cells display correctly where I entered them (9:00
> and
> 9:45) but when I merge the cell contents using the function =A4&" - "&B4 I
> get the result 0.375 - 0.406 instead of 9:00 - 9:45 as expected. The
> destination cell has the same time settings as the two original cells.
>
> How do I stop the times converting to decimals and get them to display the
> way I want them too?

 | 
Pages: 1
Prev: Explain INDEX MATCH
Next: Auto fill