SOLVED

Leap Year

%3CLINGO-SUB%20id%3D%22lingo-sub-1414123%22%20slang%3D%22en-US%22%3ELeap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414123%22%20slang%3D%22en-US%22%3E%3CP%3EFinaly%20got%20a%20working%20routine%20in%20Excel%20VBA%20to%20calculate%20if%20current%20or%20specific%20date%20is%20in%20a%20leap%20year.%3C%2FP%3E%3CP%3EPlease%20send%20me%20a%20note%20and%20I%20will%20publish%20the%20VBA%20code%20in%20English.%3C%2FP%3E%3CP%3Ekind%20regards%2C%20Eddy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1421426%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1421426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676124%22%20target%3D%22_blank%22%3E%40EExmannhotmailcom%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello!%20You've%20posted%20your%20question%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FCommunity-Discussion%2Fbd-p%2FCommunityQuestions%22%20target%3D%22_blank%22%3ECommunity%20Discussion%20space%3C%2FA%3E%2C%20which%20is%20intended%20for%20discussion%20around%20the%20Tech%20Community%20website%20itself%2C%20not%20product%20questions.%20I'm%20moving%20your%20question%20to%20the%20%5B%23%24dp5%5D%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3EExcel%20space%3C%2FA%3E%20-%20please%20post%20Excel%20questions%20here%20in%20the%20future.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1421532%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1421532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676124%22%20target%3D%22_blank%22%3E%40EExmannhotmailcom%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20in%20case%2C%20with%20formula%20you%20may%20check%20as%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(MONTH(DATE(YEAR(A1)%2C2%2C29))%3D2%2C%22leap%22%2C%20%22not%20leap%22)%0A%0Aor%0A%3DIF(DAY(DATE(YEAR(A1)%2C3%2C0))%3D29%2C%22leap%22%2C%20%22not%20leap%22)%0A%0Aor%20like%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20the%20date%20is%20in%20A1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1422601%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676124%22%20target%3D%22_blank%22%3E%40EExmannhotmailcom%3C%2FA%3E%26nbsp%3BAnother%20formula%20could%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(MOD(YEAR(A1)%2F4%2C1)%3D0%2C%22Leap%20Year%22%2C%22Not%20a%20Leap%20Year%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1422982%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESmall%20comment%20-%20that%20gives%20incorrect%20result%20for%20every%20100%2C%20but%20not%20every%20400%20year.%20For%20example%20year%202100%20is%20not%20leap.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423019%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423019%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BHmmmm...%20you%20are%20right.%20Learned%20something%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428494%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F41707%22%20target%3D%22_blank%22%3E%40Eric%20Starker%3C%2FA%3E%26nbsp%3B%20Sorry%20that%20was%20not%20my%20intend%2C%20I'm%20new%20here%20and%20could%20not%20find%20a%20solution%20within%20Excel%20(standard%20formula)%20to%20calculate%20if%20a%20date(year)%20is%20actualy%20a%20leap-year.%20In%20my%20old%20days%20as%20system%20programmer%20on%20main%20frames%20there%20where%20standard%20routines%20you%20could%20execute%20giving%20it%20a%20date%20and%20getting%20back%20details%20including%20if%20it%20was%20in%20a%20leap%20year.%3C%2FP%3E%3CP%3EThanks%20for%20your%20action.%3C%2FP%3E%3CP%3EKind%20regards%2C%20Eddy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428530%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428530%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BI'm%20getting%20an%20error%20with%20this%20formula%3A%20%22To%20Few%20Arguments%20entered%22%2C%20checked%20all%20brackets%2C%20checked%20all%20arguments%20as%20you%20gave%20in%20your%20example%2C%20but%20keep%20getting%20the%20error.%20Thanks%20for%20your%20effort.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%2C%20Eddy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551820%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551820%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20this%20work%20if%20you%20only%20have%20the%20year%3F%3F%26nbsp%3B%20Thanks!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552105%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552105%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742945%22%20target%3D%22_blank%22%3E%40Mike_Blinn%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B's%20formula%20will%20work%20for%20any%20date%20in%20that%20year.%26nbsp%3B%20If%20you%20are%20going%20to%20pass%20only%20a%20number%20(e.g..%202020)%20then%20you%20could%20use%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(MONTH(DATE(A1%2C2%2C29))%3D2%2C%22leap%22%2C%22no%20leap%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eagain%20where%20cell%20A1%20has%20the%20year%20number%20you%20are%20asking%20about.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552109%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742945%22%20target%3D%22_blank%22%3E%40Mike_Blinn%3C%2FA%3E%26nbsp%3B%2C%20If%20you%20only%20have%20the%20year%2C%20just%20remove%20the%20Year%20function%20in%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B's%20formulas%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(MONTH(DATE(A1%2C2%2C29))%3D2%2C%22leap%22%2C%20%22not%20leap%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(DAY(DATE(A1%2C3%2C0))%3D29%2C%22leap%22%2C%20%22not%20leap%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELove%20the%20simplicity%20and%20elegance%20of%20the%20formulas%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552720%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552720%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%20!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552721%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552721%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20you%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%20!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552842%22%20slang%3D%22en-US%22%3ERe%3A%20Leap%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552842%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BI%20Think%20that%20is%20correct%2C%20since%20January%201st%20of%20March%201st%20or%20any%20day%20in%20that%20given%20year%20will%20of%20will-not%20be%20in%20part%20of%20a%20Leap%20Year.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Finaly got a working routine in Excel VBA to calculate if current or specific date is in a leap year.

Please send me a note and I will publish the VBA code in English.

kind regards, Eddy

13 Replies

@EExmannhotmailcom 

Hello! You've posted your question in the Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future. 

best response confirmed by EExmannhotmailcom (New Contributor)
Solution

@EExmannhotmailcom 

Just in case, with formula you may check as

 

=IF(MONTH(DATE(YEAR(A1),2,29))=2,"leap", "not leap")

or
=IF(DAY(DATE(YEAR(A1),3,0))=29,"leap", "not leap")

or like

 

if the date is in A1.

@EExmannhotmailcom Another formula could be:

=IF(MOD(YEAR(A1)/4,1)=0,"Leap Year","Not a Leap Year")

 

@Riny_van_Eekelen 

Small comment - that gives incorrect result for every 100, but not every 400 year. For example year 2100 is not leap.

@Sergei Baklan Hmmmm... you are right. Learned something again.

@Eric Starker  Sorry that was not my intend, I'm new here and could not find a solution within Excel (standard formula) to calculate if a date(year) is actualy a leap-year. In my old days as system programmer on main frames there where standard routines you could execute giving it a date and getting back details including if it was in a leap year.

Thanks for your action.

Kind regards, Eddy

@Riny_van_Eekelen I'm getting an error with this formula: "To Few Arguments entered", checked all brackets, checked all arguments as you gave in your example, but keep getting the error. Thanks for your effort.

 

Kind Regards, Eddy

@Sergei Baklan 

 

Will this work if you only have the year??  Thanks!!!

@Mike_Blinn  @Sergei Baklan 's formula will work for any date in that year.  If you are going to pass only a number (e.g.. 2020) then you could use:

=IF(MONTH(DATE(A1,2,29))=2,"leap","no leap")

again where cell A1 has the year number you are asking about.

@Mike_Blinn , If you only have the year, just remove the Year function in @Sergei Baklan 's formulas:

=IF(MONTH(DATE(A1,2,29))=2,"leap", "not leap")
=IF(DAY(DATE(A1,3,0))=29,"leap", "not leap")

 

Love the simplicity and elegance of the formulas, @Sergei Baklan !

Thank you@mtarler  !!

 

Thanks you  @TheAntony  !!

 

@mtarler I Think that is correct, since January 1st of March 1st or any day in that given year will of will-not be in part of a Leap Year.