Concatenated Dates?

J

Jay

Hi,

I'm trying to solve a problem whereby dates are being transferred to their
1904 system value when I don't want them to.

Let's say we have the following cells:

A1 contains 01-Mar-06 [01/03/2006 with custom format dd-mmm-yy]
A2 contains 15-Jun-06 [15/06/2006 with custom format dd-mmm-yy]

A3 contains =A1&" to "&A2

A3 is a simple concatenation, with the intention of returning:

01-Mar-06 to 15-Jun-06

However, it gives: 37315 to 37421

So, how can I get the concatenation to use the formatted date & not the 1904
system value? I've tried all sorts of date formats but it always returns the
5-digit numbers.

Any help greatly appreciated.


-Jay-
 
J

Jay

Cheers Domenic,

Just the thing - & I've learnt a new function as well.

Thanks

-Jay-


Try...

=TEXT(A1,"dd-mmm-yy")&" to "&TEXT(A2,"dd-mmm-yy")

Hope this helps!

Jay said:
Hi,

I'm trying to solve a problem whereby dates are being transferred to their
1904 system value when I don't want them to.

Let's say we have the following cells:

A1 contains 01-Mar-06 [01/03/2006 with custom format dd-mmm-yy]
A2 contains 15-Jun-06 [15/06/2006 with custom format dd-mmm-yy]

A3 contains =A1&" to "&A2

A3 is a simple concatenation, with the intention of returning:

01-Mar-06 to 15-Jun-06

However, it gives: 37315 to 37421

So, how can I get the concatenation to use the formatted date & not the 1904
system value? I've tried all sorts of date formats but it always returns the
5-digit numbers.

Any help greatly appreciated.


-Jay-
Click to expand...
Click to expand...
 
Top