Hi all,

How can I change the date of a given cell to be the first
day of that month?

for eg. if I enter in A1 16-2-2002 I want to get 01-2-
2002 in the same cell

if I use =DATE(YEAR($A$1),MONTH($A$1),1) in my B1 i will
get the first day of the date entered in A1. but i want
to get it in A1 itself using code?


Any help?

TIA
Soniya

How about just hiding column A or setting its width to zero. Personally
in this situation I move column A far off to the right, leave column B
(which now is column A) prominent, and set the print area appropriately.

Soniya wrote:
Hi all,

How can I change the date of a given cell to be the first day of that
month?

for eg. if I enter in A1 16-2-2002 I want to get 01-2- 2002 in the
same cell

if I use =DATE(YEAR($A$1),MONTH($A$1),1) in my B1 i will get the
first day of the date entered in A1. but i want to get it in A1
itself using code?


Any help?

TIA Soniya
Sounds like you need to use the onchange event:

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 1) Then
If (IsDate(Target.Text)) Then
Target = (CStr(Month(Target.Text)) + "-01-" +
CStr(Year(Target.Text)))
End If
End If
End Sub

Note: My email client insists on wrapping the assignment line.

On Wed, 20 Aug 2003 02:51:09 -0700, "Soniya" wrote:

Hi all,

How can I change the date of a given cell to be the first
day of that month?

for eg. if I enter in A1 16-2-2002 I want to get 01-2-
2002 in the same cell

if I use =DATE(YEAR($A$1),MONTH($A$1),1) in my B1 i will
get the first day of the date entered in A1. but i want
to get it in A1 itself using code?


Any help?

TIA
Soniya

You need to use an event triggered macro.

Example to ensure that dates entered in the range A1:A10 will be converted to
the first of the month:

Right click on the sheet tab and select View Code.
Paste the code below into the window that opens.
Note that the error checking is very primitive, as I was not sure of what you
wanted to do here, and/or whether a data validation routine would be
appropriate.

====================
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim AOI As Range, c As Range

Set AOI = [A1:A10]

If Intersect(Target, AOI) Is Nothing Then GoTo Done

For Each c In AOI
If IsDate(c.Value) Then
On Error GoTo ForNext
c.Value = DateSerial(Year(c.Value), Month(c.Value), 1)
End If
ForNext: Next c

Done: Application.EnableEvents = True
End Sub
==================


--ron

Jerry,
Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 1) Then
If (IsDate(Target.Text)) Then
Target = (CStr(Month(Target.Text)) + "-01-" + _
CStr(Year(Target.Text)))
End If
msgbox "Date was changed"
End If
End Sub

It might give you some indication why Ron has put in

Application.EnableEvents = False
at the top of his code and reenabled them at the bottom.

--
Regards,
Tom Ogilvy


"Jerry Park" wrote in message
...
Soniya wrote:
Hi all,

How can I change the date of a given cell to be the first day of that
month?

for eg. if I enter in A1 16-2-2002 I want to get 01-2- 2002 in the
same cell

if I use =DATE(YEAR($A$1),MONTH($A$1),1) in my B1 i will get the
first day of the date entered in A1. but i want to get it in A1
itself using code?


Any help?

TIA Soniya
Sounds like you need to use the onchange event:

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 1) Then
If (IsDate(Target.Text)) Then
Target = (CStr(Month(Target.Text)) + "-01-" +
CStr(Year(Target.Text)))
End If
End If
End Sub

Note: My email client insists on wrapping the assignment line.

Yes. I was just giving basic code to accomplish the desired result.

Application.EnableEvents = False is good.

Actually, the code should also check for the possibility that multiple
cells have been selected (copy/paste) and react accordingly.



Tom Ogilvy wrote:
Jerry,
Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 1) Then
If (IsDate(Target.Text)) Then
Target = (CStr(Month(Target.Text)) + "-01-" + _
CStr(Year(Target.Text)))
End If
msgbox "Date was changed"
End If
End Sub

It might give you some indication why Ron has put in

Application.EnableEvents = False
at the top of his code and reenabled them at the bottom.