Merging Date Columns

[jdu613]

I have two columns that I want to merge into one. The first column is
as month day column 21-Aug and the second column is a year column, ie
2003. Is there a way to merge the two together? I have tried the
CONCATENATE and the merge functions without success. I am a fairly
novice/infrequent user of Excel.

Thanks.

 

[Rick Rothstein \(MVP - VB\)]

If you select any cell in the column containing entries like "21-Aug" and
look at the formula bar, you will probably find its a Date and already has a
year attached to it... the current year. If it is possible that this
column's year is not the correct one, you can do this to join its month and
day with your other column's year value. Assuming your "first column" is A
and your "second column" is B....

=DATE(B1,MONTH(A1),DAY(A1))

Rick

 

[John C]

Is your first column text? Or is it a date. If it is a date, then it already
has a year, and you could use the following formula:
=DATE(B2,MONTH(A2),DAY(A2))
If it is text, and only has 21-Aug entered, AND assuming that any dates are
entered with a number, followed immediately by a - (dash), followed
immediately by a 3 letter month abbreviation, you could use the following
formula:
=DATE(B2,TEXT(A2,"m"),LEFT(A2,FIND("-",A2)-1))

Hope this helps.

 

[Rick Rothstein \(MVP - VB\)]

=DATE(B2,MONTH(A2),DAY(A2))

That is the same formula (except for the row) that I posted. I just tested
it when the "21-Aug" is Text and the formula still worked correctly
(apparently Excel converts it to a date due to its being called by a
function requiring a date argument)... so your second formula appears not to
be needed... it looks like this one formula works in both cases.

Rick

 

[David Biddulph]

It depends to some extent whether you've got text in your cells, or dates
formatted in a particular way.
If your 21-Aug is text, you could try =A2&"-"&B2 if you want output as text,
or =--(A20&"-"&B20) and format as a date if you want Excel to treat it as a
date.
If column A contains a date from which you aren't displaying the year, and
you want to ignore the undisplayed year and replace it by 2003 from column
B, try =DATE(B2,MONTH(A2),DAY(A2)).

 

[jdu613]

That is the same formula (except for the row) that I posted. I just tested
it when the "21-Aug" is Text and the formula still worked correctly
(apparently Excel converts it to a date due to its being called by a
function requiring a date argument)... so your second formula appears notto
be needed... it looks like this one formula works in both cases.

Rick








- Show quoted text -

You guys are awesome, thanks for all of the help. It worked like a
charm!

 

[jdu613]

Is your first column text? Or is it a date. If it is a date, then it already
has a year, and you could use the following formula:
=DATE(B2,MONTH(A2),DAY(A2))
If it is text, and only has 21-Aug entered, AND assuming that any dates are
entered with a number, followed immediately by a - (dash), followed
immediately by a 3 letter month abbreviation, you could use the following
formula:
=DATE(B2,TEXT(A2,"m"),LEFT(A2,FIND("-",A2)-1))

Hope this helps.

--
John C






- Show quoted text -

Thanks, you guys are great!

 

[jdu613]

It depends to some extent whether you've got text in your cells, or dates
formatted in a particular way.
If your 21-Aug is text, you could try =A2&"-"&B2 if you want output as text,
or =--(A20&"-"&B20) and format as a date if you want Excel to treat it as a
date.
If column A contains a date from which you aren't displaying the year, and
you want to ignore the undisplayed year and replace it by 2003 from column
B, try =DATE(B2,MONTH(A2),DAY(A2)).
--
David Biddulph







- Show quoted text -

Thanks for the help! You guys are great!