Excel - Automate the data entry based on specific date

Ask a question



Issue


I have an Excel file in which I filled each day with new information on the corresponding date in column B. I also have 3 columns C, D and E: year, month, week number corresponding to that date. I would like to, while entering date manually in column B, the 3 columns C, D and E are automatically be updated.

Solution


Using VBA:
Private Sub Worksheet_Change(ByVal Target As Range)   
    If Target.Column <> 1 Or Target.Row = 1 Then Exit Sub   
    Target.Offset(0, 1) = Year(Target)   
    Target.Offset(0, 2) = Month(Target)   
    Target.Offset(0, 3) = DatePart("ww", Target, vbMonday, vbFirstFourDays)   
End Sub 
  • Example file: here.
  • Thanks to eriiic for this tip.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team