Excel - Automate the data entry based on specific date

March 2017




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


Published by deri58.
This document, titled "Excel - Automate the data entry based on specific date," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).