VBA code for date change? (Newbie)

[DekHog]

Hi....

I have 33,000 rows with jobs in them - in one of the columns there is
a date for each job. The dates are spread over days/months/years e.g.
21/10/2007, 04/03/2008. What I need to do is take every job (date) in
every individual month and pull them all in to the 7th of each month.
So, everything in Feb 2007 is now dated 07/02/2007, etc.

Can anyone help with this? (Totally new to VBA).

TIA

 

[Mike H]

Unless you have a reason there is no need to resort to VBA. In an adjacent
column type the formula:-

=DATE(YEAR(A1),MONTH(A1),DAY(7))

Drag down as required.

Mike

 

[Gary''s Student]

Let's say that column E contains the dates and that you are using dd/mm/yyy
format:

In a helper column enter:

=DATE(YEAR(E1),MONTH(E1),7)

and copy down

 

[Don Guillett]

try this
Sub makealldays7()
lr = Cells(Rows.Count, "H").End(xlUp).Row
For Each c In Range("h2:h" & lr)
c.Value = DateSerial(Year(c), Month(c), 7)
Next
End Sub

 

[DekHog]

try this
Sub makealldays7()
lr = Cells(Rows.Count, "H").End(xlUp).Row
For Each c In Range("h2:h" & lr)
c.Value = DateSerial(Year(c), Month(c), 7)
Next
End Sub

Cheers guys, fantastic - thank you.

 

[Don Guillett]

Glad to help