How to calculate calendar year

D

dinesh.sarsar

New member
Joined
Sep 8, 2014
Messages
21
Reaction score
0
Points
0
Location
India, Maharashtra Pune
Excel Version(s)
MS OFFICE Windows 10
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.

Please help.

Calendar Year
20180
20191
20201
20211
20220
 

Attachments

  • Calendar Automation.xlsx
    10.3 KB · Views: 11
Pls find the file attatched with expected result

Thanks for your time:high5:
 

Attachments

  • Calendar Automation.xlsx
    10.6 KB · Views: 11
What is the relation between dates in row 2 and years in column A.
 
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
 
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

2021-05-14_132948.png

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.
 

Attachments

  • Chandoo11169Calendar Automation.xlsm
    86.2 KB · Views: 13
Last edited:
You must log in or register to reply here.
Back
Top