Combining two dates
 

I need to combine two columns. One column has the day and month, the other
column has the year. The CONCATENATE(D4,", ",E4) equation and the =D9&",
"&E9 equations dont work

again, it looks like this
l 26-Apr l 2007 l
l 27-Apr l 2007 l
l 28-Apr l 2007 l

Combining two dates
 

Just saying that the equations "dont work" is not a very helpful description
if you want help in solving your problems.
If you tell us what formula you used, what values were in the cells leading
into the formula, what result you got, and what result you expected, that
would be a good start.

One likely problem area if you are playing with dates is the question as to
whether the cells contain numbers formatted as dates or whether they contain
text. You can check this in a number of ways. One option is to use
=ISTEXT(D4) and =ISNUMBER(D4). Another option is to try Format/ Cells/
Number/ Date to see whether the cell can be changed from 26-Apr to display
temporarily in a different format such as 26/4/08 or 26 April 2008; if it
doesn't change, you're starting with text, but if it does change it is a
number formatted as text.

Having resolved the question above, if you have a number formatted as text
and you want to use it in a concatenation formula, you'll presumably want to
use the TEXT function to format it the way you want it.
So =CONCATENATE(D4,", ",E4) might become =CONCATENATE(TEXT(D4,"dd-mmm"),",
",E4)
or =D9&", "&E9 might become =TEXT(D9,"dd-mmm")&", "&E9
Change the formatting string "dd-mmm" to suit how you want the number
presented.
--
David Biddulph

"RyanR8881" wrote in message
...
I need to combine two columns. One column has the day and month, the other
column has the year. The CONCATENATE(D4,", ",E4) equation and the =D9&",
"&E9 equations dont work

again, it looks like this
l 26-Apr l 2007 l
l 27-Apr l 2007 l
l 28-Apr l 2007 l

Combining two dates
 

What do the results actually look like and what is really in D4 and E4?

RyanR8881 wrote:

I need to combine two columns. One column has the day and month, the other
column has the year. The CONCATENATE(D4,", ",E4) equation and the =D9&",
"&E9 equations dont work

again, it looks like this
l 26-Apr l 2007 l
l 27-Apr l 2007 l
l 28-Apr l 2007 l

Combining two dates
 

If the values in column D are not Text, then try:

=DATE(YEAR(E1),MONTH(D1),DAY(D1))


--
Gary''s Student - gsnu200808


"RyanR8881" wrote:

I need to combine two columns. One column has the day and month, the other
column has the year. The CONCATENATE(D4,", ",E4) equation and the =D9&",
"&E9 equations dont work

again, it looks like this
l 26-Apr l 2007 l
l 27-Apr l 2007 l
l 28-Apr l 2007 l

Combining two dates
 

It can be done, it's long winded but works
First I made a lookup table on sheet 2
1 Jan
2 Feb
3 Mar
4 Apr
5 May
6 Jun
7 Jul
8 Aug
9 Sep
10 Oct
11 Nov
12 Dec
Then used this formula to get the results you need.
CONCATENATE(DAY(A1),"-",VLOOKUP(MONTH(A1),Sheet2!A$1:B$12,2),"-",B1)
With A1 containing your date and B1 the year. The problem came with the
month thus the lookup table.
Hope this helps.


"RyanR8881" wrote:

I need to combine two columns. One column has the day and month, the other
column has the year. The CONCATENATE(D4,", ",E4) equation and the =D9&",
"&E9 equations dont work

again, it looks like this
l 26-Apr l 2007 l
l 27-Apr l 2007 l
l 28-Apr l 2007 l

Combining two dates
 

I found the answer: =DATEVALUE(LEFT(A1,6) & "," & RIGHT(A1,5))



"James" wrote:

It can be done, it's long winded but works
First I made a lookup table on sheet 2
1 Jan
2 Feb
3 Mar
4 Apr
5 May
6 Jun
7 Jul
8 Aug
9 Sep
10 Oct
11 Nov
12 Dec
Then used this formula to get the results you need.
CONCATENATE(DAY(A1),"-",VLOOKUP(MONTH(A1),Sheet2!A$1:B$12,2),"-",B1)
With A1 containing your date and B1 the year. The problem came with the
month thus the lookup table.
Hope this helps.


"RyanR8881" wrote:

I need to combine two columns. One column has the day and month, the other
column has the year. The CONCATENATE(D4,", ",E4) equation and the =D9&",
"&E9 equations dont work

again, it looks like this
l 26-Apr l 2007 l
l 27-Apr l 2007 l
l 28-Apr l 2007 l