Excel - Automate the data entry based on specific date

December 2016




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.

Related :

This document entitled « Excel - Automate the data entry based on specific date » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.