# I need to create a formula

#### Becks37

##### New Member
Hello,

I’m stuck on a formula I need in excel. I have worked out most of them but very stuck on this one as a total novice.

I’m trying to manage students on my spreadsheets and F column has month and year they started the course and then G column says what year they are in. How do I get the sheet to automatically change the year depending on what month and year they joined the course?

Hope this makes sense, I just need to be able to have the year change itself instead of me having to manually go through every month and see if any of the students have moved into the next year of the course.

Thank you so much in advance

what version of excel are you using

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

I don’t know if this picture is any use as it’s on my work laptop I’m not sure how to send a small workable sample?
Version 2203 it says on Microsoft 365.

#### Attachments

• IMG_2339.png
61.8 KB · Views: 0
Or does this work better than the picture?

#### Attachments

• Book2.xlsx
10.5 KB · Views: 1
OK, so is there a cutoff date to change from year 1 to year 2
or is it the anniversary of the date

i have used
=DATEDIF(F3, TODAY(),"Y")+1
which counts complete years from the date entered
but does not give the correct results

So it would be a year from the date in cohort. Does it need to be an exact date to work? So it is the anniversary of them starting their course.

So ‘Sept 23’ cohort will move into year 2 in Sept 24.

so

row6
sep19 - i make 5 , but you have 4

row7
may22 - I make 2 and you have 1

if it should be 5 and 2
then
as posted
=DATEDIF(F3, TODAY(),"Y")+1

see column R for comparison

#### Attachments

• Book2-ETAF.xlsx
11.4 KB · Views: 1
Yes it should be 5 & 2. You see this is why I need Excel.

Thank you so much

I have just put that in and it says #NAME?

what have you typed in ?

its been around for quite a few versions and certainly works in 365

i have changed to include a IF - so you can copy down and works on blank cells - returning blank
=IF(F3="","",DATEDIF(F3, TODAY(),"Y")+1)

#### Attachments

• Book2-ETAF2.xlsx
11.8 KB · Views: 0
That may work better as currently if the cell is blank it return 125. Thank you.

yes, a blank cell is seen by excel as 1900

Can I lock a column with a formula in to stop people deleting it?

yes