### To determine whether a year is a leap year, follow these steps:

1. If the year is evenly divisible by 4, go to step 2. Otherwise, go to step 5.

2. If the year is evenly divisible by 100, go to step 3. Otherwise, go to step 4.

3. If the year is evenly divisible by 400, go to step 4. Otherwise, go to step 5.

4. The year is a leap year (it has 366 days).

5. The year is not a leap year (it has 365 days).

Following formula gives the results based on above steps. This considers that you have stored year in Cell A1 of your worksheet

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"Leap Year", "NOT a Leap Year")

Since excel has all these calculations built in , you can just test the last day of the February month and decide based on day value. If it is 29 then Leap Year else NOT a Leap Year.

Following formula gives the results based on above steps. This considers that you have stored year in Cell A1 of your worksheet

=IF(DAY(DATE(A1,3,0))=29,"Leap Year","NOT a Leap Year")

First Formula returns 1900 as "NOT a Leap Year" but second Formula will return it as "Leap Year". This is due to bug in excel which considers 1900 as a Leap Year though it was not a leap year.

This is why I will recommend to use second Formula in excel to determine a Leap Year since it takes care of any bug that exists in excel

### VBA Function for Leap Year Test:

Public Function IsLeapYear(ByVal YY As Long) As Boolean

IsLeapYear = Day(DateSerial(YY, 3, 0)) = 29

End Function

Some people may not see the date specification part of this formula...

ReplyDelete=IF(DAY(DATE(A1,3,0))=29,"Leap Year","NOT a Leap Year")

as referring to a February date. An alternative test which makes this a little clearer would be this...

=IF(MONTH(DATE(A1,2,29))=2,"Leap Year","NOT a Leap Year")

Another way we could do this test is as follows...

=IF(ISNUMBER(--(A1&"-02-29")),"Leap Year","NOT a Leap Year")

The only reason I mention this version is that the test uses only one function call as opposed to the two used in the previous versions. Theoretically, this should make it more efficient, although there is a loss in efficiency due to the concatenation. The VBA equivalent code for these two approaches would be...

IsLeapYear = Month(DateSerial(YY, 2, 29)) = 2

and...

IsLeapYear = IsDate("2/29/" & YearIn)

Rick Rothstein (MVP - Excel)

Hello Rick

ReplyDeleteThanks for your inputs.

Kindly advise how we convert the date 13.08.2012 to 08/13/2012 format.

ReplyDeleteRight Click

DeleteClick Format Cells

Click Custom

Type dd/mm/yyyy

yogesh ji,

ReplyDeletei need help to create a order form in excel,by merging to excel file,

for both example file i want to mail you,

if possiable kindly provide your mail id, or other way to post that files,

kindly advise

regards

harish nebhwani

sultanate of oman

=IF(MOD(A1,4)=0,"Leap Year","Non Leap Year")

ReplyDeleteyogesh sir, pl help I want to numeric date of birth in text in exel. for example 12/10/2016 conver into twelve october two thousand sixteen. ceate formulae in exel

ReplyDeleteYour post are inspiring. I hope you will write more such post. Each of your post brings a lot of good.

ReplyDeleteDigital Marketing Course in Kolkata

It is really a great and helpful piece of info. I am glad that you shared this helpful information with us. Please keep us informed like this. Thank you for sharing.

ReplyDeleteProject Management Courses In Hyderabad

I was very happy to find this site. I really enjoyed reading this article today and think it might be one of the best articles I have read so far. I wanted to thank you for this excellent reading !! I really enjoy every part and have bookmarked you to see the new things you post. Well done for this excellent article. Please keep this work of the same quality.

ReplyDeleteData Science Course in Bangalore

Wow, happy to see this awesome post. I hope this think help any newbie for their awesome work and by the way thanks for share this awesomeness, i thought this was a pretty interesting read when it comes to this topic. Thank you..

ReplyDeleteArtificial Intelligence Course

I need to thank you for this very good read and i have bookmarked to check out new things from your post. Thank you very much for sharing such a useful article and will definitely saved and revisit your site.

ReplyDeleteData Science Course

Excellent Blog! I would like to thank you for the efforts you have made in writing this post. Gained lots of knowledge.

ReplyDeleteData Analytics Course

What an incredible message this is. Truly one of the best posts I have ever seen in my life. Wow, keep it up.

ReplyDeleteAI Courses in Bangalore

Your site is truly cool and this is an extraordinary moving article and If it's not too much trouble share more like that. Thank You..

ReplyDeleteDigital Marketing Course in Hyderabad

Awesome article. I enjoyed reading your articles. this can be really a good scan for me. wanting forward to reading new articles. maintain the nice work!

ReplyDeleteData Science Courses in Bangalore

Thank a lot. You have done excellent job. I enjoyed your blog . Nice efforts

ReplyDeleteData Science Certification in Hyderabad

I am sure it will help many people. Keep up the good work. It's very compelling and I enjoyed browsing the entire blog.

ReplyDeleteBusiness Analytics Course in Bangalore

I bookmarked your website because this site contains valuable information. I am very satisfied with the quality and the presentation of the articles. Thank you so much for saving great things. I am very grateful for this site.

ReplyDeleteData Science Training in Bangalore

Wonderful blog found to be very impressive to come across such an awesome blog. I should really appreciate the blogger for the efforts they have put in to develop such amazing content for all the curious readers who are very keen on being updated across every corner. Ultimately, this is an awesome experience for the readers. Anyways, thanks a lot and keep sharing the content in the future too.

ReplyDeleteDigital Marketing Training in Bangalore

I bookmarked your website because this site contains valuable information. I am very satisfied with the quality and the presentation of the articles. Thank you so much for saving great things. I am very grateful for this site.

ReplyDeleteArtificial Intelligence Training in Bangalore

Truly incredible blog found to be very impressive due to which the learners who go through it will try to explore themselves with the content to develop the skills to an extreme level. Eventually, thanking the blogger to come up with such phenomenal content. Hope you arrive with similar content in the future as well.

ReplyDeleteMachine Learning Course in Bangalore