Macro to set start date to 1st of month?

[tonywatsonhelp]

Hi Everyone,
Hope you can help with this,

I have data in column C which is start date. now I've tried asking people to always make this the 1st of the month but sometime they are still input the date it started for example 20/08/2017
So I need a macro to correct this as my report are set to look for the 1st.

So what I need is a macro that can look at data each time its put into a cell in Column C, and if its not the 1st if the month change it to the first of that month?

any ideas how to do this?

Thanks

Tony

 

[mrhstn]

One method might be to pick an arbitrary column (BA in this example) to perform a calculation then put it back to column C, like so...

Sub Macro1()
Dim lst As Long
lst = Range("C" & Rows.Count).End(xlUp).Row
With Range("BA2:BA" & lst)
    .Formula = "=DATE(YEAR(C2), MONTH(C2), 1)"
    Range("C2:C" & lst) = .Value
    .Clear
End With
End Sub

 

[Peter_SSs]

... look at data each time its put into a cell in Column C, and if its not the 1st if the month change it to the first of that month

I think this should do that for you. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("C"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If IsDate(c.Value) Then c.Value = c.Value - Day(c.Value) + 1
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[tonywatsonhelp]

Thanks Peter,
This is great

Tony

 

[Peter_SSs]

Thanks Peter,
This is great

Tony

Cheers, thanks for letting us know.

Of course, the method is defeated if the user does not have macros enabled.