• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need to create a formula


New Member

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.


  • IMG_2339.png
    61.8 KB · Views: 0
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
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.

sep19 - i make 5 , but you have 4

may22 - I make 2 and you have 1

if it should be 5 and 2
as posted

see column R for comparison


  • Book2-ETAF.xlsx
    11.4 KB · Views: 1
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)


  • Book2-ETAF2.xlsx
    11.8 KB · Views: 0