Results 1 to 7 of 7

Thread: How to calculate calendar year

  1. #1
    Acolyte dinesh.sarsar's Avatar
    Join Date
    Sep 2014
    Location
    India, Maharashtra Pune
    Posts
    20
    Articles
    0
    Excel Version
    MS OFFICE Windows 10

    How to calculate calendar year



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    2018 0
    2019 1
    2020 1
    2021 1
    2022 0
    Attached Files Attached Files

  2. #2
    Seeker kvsrinivasamurthy's Avatar
    Join Date
    Jun 2014
    Posts
    8
    Articles
    0
    Excel Version
    2019
    Pl show the expected result in the file.

  3. #3
    Acolyte dinesh.sarsar's Avatar
    Join Date
    Sep 2014
    Location
    India, Maharashtra Pune
    Posts
    20
    Articles
    0
    Excel Version
    MS OFFICE Windows 10
    Pls find the file attatched with expected result

    Thanks for your time
    Attached Files Attached Files

  4. #4
    Seeker kvsrinivasamurthy's Avatar
    Join Date
    Jun 2014
    Posts
    8
    Articles
    0
    Excel Version
    2019
    What is the relation between dates in row 2 and years in column A.

  5. #5
    Acolyte dinesh.sarsar's Avatar
    Join Date
    Sep 2014
    Location
    India, Maharashtra Pune
    Posts
    20
    Articles
    0
    Excel Version
    MS OFFICE Windows 10
    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. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,948
    Articles
    0
    Excel Version
    365
    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

    Click image for larger version. 

Name:	2021-05-14_132948.png 
Views:	3 
Size:	4.7 KB 
ID:	10501

    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.
    Attached Files Attached Files
    Last edited by p45cal; 2021-05-14 at 06:59 PM.

  7. #7
    Acolyte dinesh.sarsar's Avatar
    Join Date
    Sep 2014
    Location
    India, Maharashtra Pune
    Posts
    20
    Articles
    0
    Excel Version
    MS OFFICE Windows 10
    Thanks for your time p45cal this serves my purpose,

    Love you

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •