## Sunday, October 18, 2009

### Excel Dates : Leap Year or Not a Leap Year

Excel follows Gregorian calendar which was first established in 1582 by Pope Gregory XIII.

### 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)) = 29End Function`

## Leap Year , leap year test , leap year check , check leap year , Leap Year in Excel

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

=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)

2. Hello Rick

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

1. Right Click
Click Format Cells
Click Custom
Type dd/mm/yyyy

4. yogesh ji,

i 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,

regards

harish nebhwani

sultanate of oman

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

6. yogesh 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

7. Your post are inspiring. I hope you will write more such post. Each of your post brings a lot of good.
Digital Marketing Course in Kolkata

8. 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.

9. 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.
Data Science Course in Bangalore

10. 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..
Artificial Intelligence Course

11. 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.
Data Science Course

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

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

14. 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..

15. 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!
Data Science Courses in Bangalore

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

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

18. 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.

Data Science Training in Bangalore

19. 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.

Digital Marketing Training in Bangalore

20. 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.

Artificial Intelligence Training in Bangalore

21. 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.

Machine Learning Course in Bangalore

22. Great post happy to see this. I thought this was a pretty interesting read when it comes to this topic Information. Thanks..
Artificial Intelligence Course

23. Nice Post thank you very much for sharing such a useful information and will definitely saved and revisit your site and i have bookmarked to check out new things frm your post.
Data Science Course

24. Thanks Your post is so cool and this is an extraordinary moving article and If it's not too much trouble share more like that.

25. You have done excellent job Thanks a lot and I enjoyed your blog. Great Post.

26. Really impressed! Everything is a very open and very clear clarification of the issues. It contains true facts. Your website is very valuable. Thanks for sharing.

Digital Marketing Training in Bangalore

27. A good blog always contains new and exciting information, and reading it I feel like this blog really has all of these qualities that make it a blog.

Artificial Intelligence Training in Bangalore

28. Happy to chat on your blog, I feel like I can't wait to read more reliable posts and think we all want to thank many blog posts to share with us.

Machine Learning Course in Bangalore

29. I wanted to leave a little comment to support you and wish you the best of luck. We wish you the best of luck in all of your blogging endeavors.

Data Science Training in Bangalore

30. It is a very helpful and very informative blog. I really learned a lot from it thanks for sharing.
Data Analytics Course

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

32. Very informative blog! There is so much information here that can help thank you for sharing.
Data Science Syllabus

33. This is an informative and knowledgeable article. therefore, I would like to thank you for your effort in writing this article.
Best Digital Marketing Courses in Bangalore

34. A good blog always contains new and exciting information and as I read it I felt that this blog really has all of these qualities that make a blog.

Digital Marketing Institute in Bangalore

35. You have done a great job and will definitely dig it and personally recommend to my friends. Thank You.
Data Science Online Training

36. Superb Information and really appreciated with it and this is fine to read and valuable. I like it.
Digital Marketing Course fees in Hyderabad

37. The blog is informative and very useful therefore, I would like to thank you for your effort in writing this article.
Data Analytics Course in Lucknow

38. Really this article is truly one of the best in article history and am a collector of old "items" and sometimes read new items if I find them interesting which is one that I found quite fascinating and should be part of my collection. Very good work!
Data Scientist Course in Gurgaon

39. I really appreciate this wonderful message you have given us. I assure you that would be beneficial for most people.

Data Analytics Course in Nagpur

40. I have read your article, it is very informative and useful to me, I admire the valuable information you offer in your articles. Thanks for posting it ...

41. A good blog always contains new and exciting information and as I read it I felt that this blog really has all of these qualities that make a blog.
Data Science Institutes in Bangalore

42. Very informative message! There is so much information here that can help me thank you for sharing
Data Analytics Course in Lucknow

43. So luck to come across your excellent blog, glad i found it. Keep posting new articles. Good luck.
Data Science Course Details

44. I really appreciate this wonderful message you have given us. I assure you that would be beneficial for most people.

Data Science Course in Nagpur

45. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it, Keep posting new articles.

46. I finally found a great article here. Quality postings are essential to get visitors to visit the website, that's what this website offers.
Data Science Training in Jabalpur

47. Thank you for sharing this wonderful blog, I read that Post and got it fine and informative. Please share more like that...

48. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Thanks for sharing.
Data Scientist Course in Jabalpur

49. Really impressed! Information shared was very helpful Your website is very valuable. Thanks for sharing.

50. This is such a great resource that you are providing and you give it away for free.

Data Analytics Course in Durgapur

51. I am impressed by the information that you have on this blog. It shows how well you understand this subject.

Data Science Institutes in Bangalore

53. Good blog and absolutely exceptional. You can do a lot better, but I still say it's perfect. Keep doing your best.

Data Scientist Training in Bangalore

54. I think this is a really good article. You make this information interesting and engaging. Thanks for sharing.
Data Science Course in India

55. I am delighted to discover this page. I must thank you for the time you devoted to this particularly fantastic reading !! I really liked each part very much and also bookmarked you to see new information on your site.