Change Date entered to 1st of the month

[theheed]

Been trying a few methods to either use validation or VB to either force or convert when entered a date to the 1st

I thought about using Datepart to de-construct then reconstruct but this is a bit messy.

i.e. User enters 02/02/11, it changes to 01/02/11

The date column is V for reference

Any ideas??

 

[Andrew Poulsom]

Here's an example:

Sub Test()
    Dim D As Date
    D = DateValue("02/01/2011")
    MsgBox D
    D = DateSerial(Year(D), Month(D), 1)
    MsgBox D
End Sub

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 22 And IsDate(Target.Value) Then
    Application.EnableEvents = False
    Target.Value = DateSerial(Year(Target.Value), Month(Target.Value), 1)
    Application.EnableEvents = True
End If
End Sub

 

[JamesW]

Hi there,

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("V1")) Is Nothing Then
            Target.Value = Target.Value - Day(Target.Value) + 1
        End If
End Sub

Change If Not Intersect(Target, Range("V1")) to the Range you want.

 

[theheed]

.

 

[theheed]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 22 And IsDate(Target.Value) Then
    Application.EnableEvents = False
    Target.Value = DateSerial(Year(Target.Value), Month(Target.Value), 1)
    Application.EnableEvents = True
End If
End Sub

Works a treat, it is a lot neater than I would have managed