Microsoft Excel and Myself

Perhaps there is some guilt hidden in my words when I say that I have never really acted on the maxim "When you've got it, flaunt it"!

Excel and I go back a long, long way - and yes, VBA Macro's came in much later, coz I used to do Macro Programming in Lotus 1-2-3 long before I began coding in Visual Basic for Applications. Funny thing is, that I never did it commercially, so far. It was always either for self-consumption, or for my CA Firm, where I had been a partner at that time.

Today, practically everyone who needs to compute figures, uses Excel Spreadsheets, VBA or no VBA. Ironically, a VBA subroutine, if coded properly, can save you hours of manual computing, a point I have proven over and over, in the past, since almost 1995, when I entered private practice, as a CA.

Today, even though Microsoft says that VBA will continue to be a part of the Office Suite, be it Office 2024/25 or Microsoft 365, there are maybe just a handful of CA's who use VBA, that too just the Macro Recorder!! Have yet to come across somebody professional regularly creating or actively using UDF's, userforms or class modules, which is where the true power lies!!

Not that I am complaining, because in any case, I am not doing what I currently do or have been doing commercially, so if I don't earn another rupee, it does not matter!!

Creating formats in Excel is another hobby that I have always had, and I have enough in my Formats folder to keep me going forever. Templates are for those who do not have time to create a format, but I have always carved out time from my schedule to create my own templates, but none that I can share publicly, unfortunately!! Yes, occasionally I too use office templates, like Amortisation of Term Loans, but only to adapt them for my own use.

RevisedMethod2DetermineLeapYear

https://www.facebook.com/groups/cavsm.vbagang

Method to determine whether a year is a leap year 

(Revised on 09-Oct-2019)

(Source: https://support.microsoft.com/help/214019)

• Applies to:

  Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2003

Summary

This article describes how to determine whether the year in a date that is used in a Microsoft Excel document is a leap year.

More Information

The date system that is used by Excel is based on the Gregorian calendar, first established in 1582 by Pope Gregory XIII. This calendar was designed to correct the errors introduced by the less accurate Julian calendar.

In the Gregorian calendar, a normal year consists of 365 days. Because the actual length of a sidereal year (the time required for the Earth to revolve once about the Sun) is actually 365.25635 days, a "leap year" of 366 days is used once every four years to eliminate the error caused by three normal (but short) years. Any year that is evenly divisible by 4 is a leap year: for example, 1988, 1992, and 1996 are leap years.

However, there is still a small error that must be accounted for. To eliminate this error, the Gregorian calendar stipulates that a year that is evenly divisible by 100 (for example, 1900) is a leap year only if it is also evenly divisible by 400.

For this reason, the following years are not leap years:

1700, 1800, 1900, 2100, 2200, 2300, 2500, 2600

This is because they are evenly divisible by 100 but not by 400.

The following years are leap years: 1600, 2000, 2400

This is because they are evenly divisible by both 100 and 400.

Because versions of Microsoft Excel earlier than Excel 97 handle only years from 1900 to 2078, only the year 1900 is subject to the 100/400 exclusion rule of leap years in Microsoft Excel. However, in order to be compatible with other programs, Microsoft Excel treats the year 1900 as a leap year.  

How to determine whether a year is a leap year

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

Formula to determine whether a year is a leap year

Use the following formula to determine whether the year number that is entered into a cell (in this example, cell A1) is a leap year:

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

If the value in cell A1 is this	The formula returns
1992	Leap Year
2000	Leap Year
1900	NOT a Leap Year