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.
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)
- Example file: here.
- Thanks to eriiic for this tip.
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