Merging year and date column into one date column

[Koala20171990]

Hi all,

Another quick question: I currently have a spreadsheet that has a year column and an effective date column which has the month and day. I'm looking for a macro that will merge the two columns into one with the format (month-day-year). Any advice is greatly appreciated!
Right now, the format looks like this:
[TABLE="width: 97"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Effective
Date[/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD="align: right"]19-May[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1-Jul[/TD]
[/TR]
[TR]
[TD="align: right"]2001[/TD]
[TD="align: right"]19-May[/TD]
[/TR]
[TR]
[TD]Thanks![/TD]
[TD="align: right"]1-Jul

​

[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Scott T]

Where is this located, what columns? is the effective date text or an Excel date? Where do you wan the merged date?

 

[Koala20171990]

The columns are located within a table, the year column is in column B and the effective date is in column C, I want the combined date to be in a new column (column D). The date is an Excel date in the format d-mmm.

 

[legento]

[TABLE="width: 548"]
<tbody>[TR]
[TD]2001[/TD]
[TD]19 Jul[/TD]
[TD]=MONTH(B1)[/TD]
[TD]=DAY(B1)[/TD]
[TD]=DATE(A1,C1,D1)[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]


dont know if this will help

 

[Koala20171990]

This would work if there's a matching year entry for every effective date entry. The problem is that multiple effective dates are categorized under the same year so there are blanks in the year column when the year remains consistent but the effective date is changed.

For example
[TABLE="width: 97"]
<tbody>[TR]
[TD="align: right"]2000[/TD]
[TD="align: right"]19-May[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1-Jul[/TD]
[/TR]
[TR]
[TD="align: right"]2001[/TD]
[TD="align: right"]19-May[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1-Jul[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
There are two effective dates for the year 2000 and 2001, so there's a blank in the second and fourth rows. Using the date function in those rows would not yield the correct date.

 

[Jonmo1]

You should fill in those blanks, so that every record has a Year specified. (this will help in the long run, not just for this task)
If it's a matter of having so many records that it would be too much work, it's really not.
You can do them all at once.

You can highlight that column with the years
Press CTRL+G
Click Special
Select Blanks
Click OK
Press =
Press UP Arrow
Pess CTRL + ENTER

 

[Jonmo1]

And here's another method as well..

=EDATE(B1,-(YEAR(B1)-A1)*12)

A1 = Year
B1 = Effective DAte

 

[Scott T]

I agree with Jonmo1 you are better off with the year in every record.

This should work if you do not add the year to every record.

Sub mergedate()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To lr
    If Range("B" & x) = "" Then
    Else
        myyear = Range("B" & x)
    End If
    
    mymonth = Month(Range("C" & x))
    myday = Day(Range("C" & x))
    Range("D" & x) = DateValue(mymonth & "/" & myday & "/" & myyear)
Next x

End Sub