# Change a value if date changes

##### Board Regular
I have a spreadsheet that I use with my students in school. There is a column which shows which year group they are in (numbered from 1-6). Every September this increases by 1, i.e. from year 1 to year 2 etc. Is there a formula that will automatically change this for me, or do I need to write some VBA code to do this?
Thanks

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I have a spreadsheet that I use with my students in school. There is a column which shows which year group they are in (numbered from 1-6). Every September this increases by 1, i.e. from year 1 to year 2 etc. Is there a formula that will automatically change this for me, or do I need to write some VBA code to do this?
Thanks

It is possible that you could come up with a formula to figure this out. You would need to have the year or date that a student started to get it. Do you have that information available?

Can you provide a sampling of your data?

No need for a formula. Simply enter a 1 in a blank cell and copy. Then select your column and choose Edit>Paste Special>Add>OK. You're done!!
lenze
PS: You can delete the 1.

Last edited:
Yes I have a start date column, it is in mm/yyyy format. Would I need a nested 'if' statement?

No need for a formula. Simply enter a 1 in a blank cell and copy. Then select your column and choose Edit>Paste Special>Add>OK. You're done!!
lenze
PS: You can delete the 1. You may also have to reformat your cells to date
Mind you this sounds a lot easier! Thanx lenze

lenze's solution will work and will need to be repeated each year.

Here is the formula solution that I came up with:

=IF(MONTH(TODAY())<9,YEAR(TODAY())-YEAR(C2),YEAR(TODAY())-YEAR(C2)+1)
Book1
ABCD
1LastFirstStartDateYearGroup
2DoeJane9/20072
3SmithJohn9/20081
4TaylorJames9/20054
Sheet2

Thanks BAlGaInTl, that will do nicely!

The formula above can just be start date in column A then in B and drag along or enter in non contiguous cells , =A+365 formatted as mm yyyy

Replies
3
Views
166
Replies
7
Views
199
Replies
4
Views
127
Replies
9
Views
98
Replies
1
Views
200

1,207,091
Messages
6,076,522
Members
446,212
Latest member
KJAYPAL200

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back