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



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

23 comments:

  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)

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

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

      Delete
  3. 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,

    kindly advise

    regards

    harish nebhwani

    sultanate of oman

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

    ReplyDelete
  5. 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

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

    ReplyDelete
  7. 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.
    Project Management Courses In Hyderabad

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

    ReplyDelete
  9. 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

    ReplyDelete
  10. 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

    ReplyDelete
  11. 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

    ReplyDelete
  12. 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

    ReplyDelete
  13. 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..
    Digital Marketing Course in Hyderabad

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

    ReplyDelete
  15. Thank a lot. You have done excellent job. I enjoyed your blog . Nice efforts
    Data Science Certification in Hyderabad

    ReplyDelete
  16. I am sure it will help many people. Keep up the good work. It's very compelling and I enjoyed browsing the entire blog.
    Business Analytics Course in Bangalore

    ReplyDelete
  17. 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

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

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

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

    ReplyDelete

Learn how to create Excel dashboards.
Yogesh Gupta's Excel Tips