I need to create a formula

[Becks37]

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

 

[ETAF]

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.

 

[Becks37]

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.

 

[Becks37]

Or does this work better than the picture?

 

[ETAF]

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

 

[Becks37]

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.

 

[Becks37]

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

 

[ETAF]

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

 

[Becks37]

Yes it should be 5 & 2. You see this is why I need Excel.

Thank you so much

 

[Becks37]

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

 

[ETAF]

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)

 

[Becks37]

That may work better as currently if the cell is blank it return 125. Thank you.

 

[ETAF]

yes, a blank cell is seen by excel as 1900

 

[Becks37]

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

 

[ETAF]

yes
password protect it

i have just searched on google for how to lock a range - as thats probably easier to goto a link then explain all the steps here

How To Lock Cells In Excel (step by step procedure) || Simplilearn

Know how to lock cells in excel. Get the correct formula and a detailed walk through on how to Lock specific cells, Lock formula cells.

www.simplilearn.com

or a youtube video