Automatically changing a cell value based on an annual date

[jlax87]

Hi there,

I am a teacher at a high school and am currently creating an Excel workbook to record students who need learning adjustments made.
On the table I have various columns of data, with student names listed in column B with their CURRENT year level in column A.
I’m not sure this is possible, but ideally I would like the year group (be it 7, 8, 9, 10, 11 or 12) to automatically change to the next value up at round the 7^th November every year. This would save me or someone else going in and updating all the students year groups to the next year (i.e. On November 7^th each year, all student names assigned as year “7” would change to show “8”; all year “8” values would change to “9”.
Obviously this could be managed relatively quickly manually or with a macro, but it would be great it it worked by itself.

I tried creating a column showing the =today() function and then tried to build conditional formatting equation on the Year Level column in column A, but I am not skilled in that particular area so bombed out pretty quickly.

Would anyone have any ideas about how to achieve the outcome I am desiring?


Thank you!

Jess

 

[JoeMo]

In some empty cell you could enter the student's start date and in an adjacent cell the start group. Then at any point in time you could use the start date and TODAY() to determine how many years have elapsed since the student started and increment the start group number accordingly.

 

[JLGWhiz]

This is a little more elaborate than JoeMo's suggestion.
Since there is no guarantee that the file will be opened on exactly the 7th of November, The procedure below uses that date as the criteria date but allows the code to execute after that date but only once for the remainder of the year. Once executed, it will not again allow the changes until the following year. I used Range("ZZ1") in the procedure for the reference range to hold the Nov 7 date criteria and Ramge("ZZ2") for controlling the code exection.. If you are using these cells for another purpose on that worksheet, then change the code in all places where those two appear to a cells of your preference. This code is Workbook event code and must be installed in the ThisWorkbook code module. To access the code module, press Alt + F11, then double click ThisWorkbook in the small Project pane at upper left in the VB Editor window. Copy the procedure into that pane, close the VB Editor and save the workbook as a macro enabled workbook .xlsm . That will preserve the code when you close the workbook. Note that I used Sheet(1) as the reference sheet. You should change that to the actual sheet name as shown on the sheet name tab and enclose it in quotation marks, ie. Sheets("mySheet")

Private Sub Workbook_SheetActivate(ByVal sh As Object)
If sh.Range("ZZ2").Value > 0 And Month(Date) < 11 Then sh.Range("ZZ2") = 0
Set sh = Sheets(1) 'Edit sheet name
If Range("ZZ2") > 0 Then Exit Sub
Dim c As Range
dt = "11/7/" & Year(Date)
sh.Range("ZZ1") = Format(dt, "mm/dd/yy")
If Date >= CDate(sh.Range("ZZ1").Value) Then
    For Each c In sh.Range("A2", Cells(Rows.Count, 1).End(xlUp))
        If c <> "" Then
            c.Value = c + 1
        End If
    Next
End If
Range("ZZ2") = sh.Range("ZZ2").Value + 1
End Sub

How the code works. When you open the workbook, the code will first look to see if the month is prior to November and if so will reset Range ZZ2 to 0. Then it will check sheet(1), or the sheet name you use, is the active sheet. If it is, it will then check the value of Cell ZZ2 to see if it is greater than 1. If it is, the the change should already have been made and it will exit the procedure without further action. If Cell ZZ2 = 0 or is blank then it will make sure that Cell ZZ1, or a cell designated by you, equals November 7, of the current year. It will then check the current date to see if it is greater than the date in Cell ZZ1 and If so will then add 1 to each value in column B. It will then add a value of 1 to Cell ZZ2, which will prevent the code from executing and changing column B values again until Cell ZZ2 again is = 0 or blank.

 

[jlax87]

Thanks for your suggestion, Joel.
JLGWhiz – thanks so much! Ok, whilst I cannot confirm that what I did worked – I followed your instructions exactly – would you be able to take a look at a sample version of my table (I can't seem to attach it here) to see whether I did it correctly? Sorry to be a pain.

I’m not sure if I am meant to see any value in the ZZ2 cell (I used goto to have a look but nothing was there) or whether I had to manually add a date to that cell so the code would work.
Obviously it is past 7 November now, so maybe the code hasn’t worked due to that reason, but I wanted to test it to make sure it works. Do you know how I can test it? In future years, will it trigger the change when the document is opened either on 7 November or later? Or will it happen automatically even if the document is closed?

I really appreciate your fast response to my query. It was a great help! Thank you!!


Jess

 

[Peter_SSs]

Obviously this could be managed relatively quickly manually ...

I agree & would probably just go with that.
1. Put a 1 in a vacant cell and Copy that cell.
2. Select your year level cells (A2:A?)
3. Paste Special... -> Operation: Add -> OK
4. Clear the '1' cell that you copied.
Done

 

[JLGWhiz]

JLGWhiz – thanks so much! Ok, whilst I cannot confirm that what I did worked – I followed your instructions exactly – would you be able to take a look at a sample version of my table (I can't seem to attach it here) to see whether I did it correctly? Sorry to be a pain.
I’m not sure if I am meant to see any value in the ZZ2 cell (I used goto to have a look but nothing was there) or whether I had to manually add a date to that cell so the code would work.
Obviously it is past 7 November now, so maybe the code hasn’t worked due to that reason, but I wanted to test it to make sure it works. Do you know how I can test it? In future years, will it trigger the change when the document is opened either on 7 November or later? Or will it happen automatically even if the document is closed?

Perhaps the code is a bit too much for someone unfamiliar with VBA. Peter and JoMoe seem to be offering more practical solutions for a lay user.

 

[JLGWhiz]

However, I did find one anomaly in the code and for sake of continuity, here is the modified version.

Private Sub Workbook_SheetActivate(ByVal sh As Object)
If sh.Range("ZZ2").Value > 0 And Month(Date) < 11 Then sh.Range("ZZ2") = 0
Set sh = Sheets(1) 'Edit sheet name
If Range("ZZ2") > 0 Then Exit Sub
Dim c As Range
dt = "11/7/" & Year(Date)
sh.Range("ZZ1") = Format(dt, "mm/dd/yy")
If Date >= CDate(sh.Range("ZZ1").Value) Then
    For Each c In sh.Range("A2", sh.Cells(Rows.Count, 1).End(xlUp))
        If c <> "" Then
            c.Value = c + 1
        End If
    Next
End If
Range("ZZ2") = sh.Range("ZZ2").Value + 1
End Sub