Starting Date of the Month

  • Thread starter Thread starter Soniya
  • Start date Start date
S

Soniya

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 said:
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
Click to expand...
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.
 
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
Click to expand...

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.
 
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.
 
Back
Top