# Thread: How to calculate calendar year

1. ## How to calculate calendar year

Hi,
I need to automate the calculation of calendar year, in the attached file, how do i get it.

The criteria is if the calendar year is complete than the count should be 1 for that year, if the calendar year is not completed than the count should be zero.

 Calendar Year 2018 0 2019 1 2020 1 2021 1 2022 0

2. Pl show the expected result in the file.

3. Pls find the file attatched with expected result

4. What is the relation between dates in row 2 and years in column A.

5. Hi,

Thanks for your time, I the date 04-05-2018 is the start date and 03-05-2022 is the end date for exact 4 year now we have some points system, for every calendar year for that person, so when the person completes a calendar year we allocated the required to him. Currently we are doing it manually and there are more than 1300 hundred people, so need to automate it. So is there any way that i can calculate the completer calendar year in excel and omit the ones which are not complete

6. In the attached, in cells C5:C9 is a formula to give the same results you have using built-in functions.
From those wanted results it looks as if you want to count the number of periods from 1st Jan to 31st Dec (calendar years).
There is a DateDif (hidden) function in Excel but it doesn't work well for what you want, but there is a macro version of the same function (DateDiff) which does, so I've made a user-defined function (UDF) in the attached, used in cell C2:
=CompleteCalendarYears(A2,B2)
It may need a little tweak depending on when you want to count a year as complete; see in this picture

that the dates from 3rd March 2021 to 31st December 2022 isn't showing as a complete year (because Excel sees 31st December as the beginning of the last day of the year whereas the full year really ends at the end of that day). You can tweak it yourself by changing the formula to:
=CompleteCalendarYears(A2,B2+1)
or we can change the user-defined function.
The UDF takes a few seconds here for about 1700 rows so be patient if you've got a lot of dates.

Is that what you were looking for?

That udf:
Code:
```Function CompleteCalendarYears(SD, ED)
CompleteCalendarYears = Application.Max(0, DateDiff("yyyy", SD, ED) - 1)
End Function```
ps. If I've got this all wrong then the (hidden) built-in worksheet function DateDif will probably do what you want.

7. Thanks for your time p45cal this serves my purpose,

Love you