Point well taken Dave.........I just used MY format (figuring everybody else
does the same) and failed to observe the OP's format........

thanks for the head's up

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote:

I think that this could be dangerous.

num1 = Format(num1, "m/1/yyyy")

It relies on excel parsing that entry. If your date settings are mdy, then no
problem. But if your settings are dmy, you may not get the date you want--and
if your settings are ymd, you may not even get a date.

I think I'd use:
num1 = dateserial(year(num1),month(num1),1)

And apply a nice unambiguous .numberformat to that range so that I could verify.



CLR wrote:

A Macro approach, assuming your dates in column A..........

Sub ChangeDayTo1stOfMonth()
'======================================
'Changes all dates in column A to the first day of the month
'======================================
Dim lastrow As Long, r As Long
Dim num1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For r = lastrow To 1 Step -1
If Cells(r, "A") 0 Then
Cells(r, "A").Select
num1 = Selection.Value
End If
num1 = Format(num1, "m/1/yyyy")
With ActiveCell
.Value = num1
End With
Next r
End Sub

Vaya con Dios,
Chuck, CABGx3

"R Storey" wrote:

I have recorded a macro to find and replace all the dates but when I run it
it doesn't actually change them. I want to change all the dates so that they
are all the 1st of the the month instead of specifying the day. I can't think
of an easier way of doing it so if anyone has any better ideas it'll be very
appreciated!!!

Here's the code:

Columns("U:U").Select
Selection.Replace What:="??/12/2005", Replacement:="01/12/2005",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/01/2006", Replacement:="01/01/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/02/2006", Replacement:="01/02/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/03/2006", Replacement:="01/03/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

It doesn't come up with an error, it just doesn't do it!

Thanks in advance!!!!

--

Dave Peterson