VBA code for date change? (Newbie)
 

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

VBA code for date change? (Newbie)
 

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

"DekHog" wrote:

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

VBA code for date change? (Newbie)
 

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
--
Gary''s Student - gsnu200726


"DekHog" wrote:

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

VBA code for date change? (Newbie)
 

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

--
Don Guillett
SalesAid Software

"DekHog" wrote in message
oups.com...
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

VBA code for date change? (Newbie)
 

On 3 Jun, 12:56, "Don Guillett" wrote:
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

--
Don Guillett
SalesAid Software
"DekHog" wrote in message

oups.com...

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

Cheers guys, fantastic - thank you.

VBA code for date change? (Newbie)
 

Glad to help

--
Don Guillett
SalesAid Software

"DekHog" wrote in message
ups.com...
On 3 Jun, 12:56, "Don Guillett" wrote:
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

--
Don Guillett
SalesAid Software
"DekHog" wrote in message

oups.com...

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

Cheers guys, fantastic - thank you.