- 589K Members
- 5,644 Online
- 715K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Leap year formula

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2019 09:43 AM

Over the years I've run into several situations where we've needed to determine if the year is a leap year; most recently to determine the number of working days to calculate billable time - not the important part. I remember being taught as a kid the easy way to figure it out in your head but couldn't find a simple way to do in an Excel formula/cell.

**Figure out in your head:** I'm realizing a lot of the tricks I was shown as a kid may not be making it into the school system or are just lost arts of thinking...so I will share and hopefully you will learn something new as well.

Looking at positions 3 and 4 of the year is all you need to determine the leap year.

* Is position 3 odd or even?* (in 2019 this is the "1")

* Odd:* then only the last position of

* Even:* then the last positions of

Feel free to check me on any year....1944 - yep a leap year. 1972 - also a leap year. Knock yourself out.

**Excel formula**

*So even with this knowledge it is not easy to turn this into usable information in Excel...until this formula. It is applying the same logic above, into a single field formula.*

Spoiler

When you put it all together this is what it looks like.

=IF(MOD(MID(YEAR($A3),3,1),2)=0,IF(MOD(MID(YEAR($A3),4,1),4)=0,"Leap","No"),IF(OR(MID(YEAR($A3),4,1)="2",MID(YEAR($A3),4,1)="6"),"Leap","No"))

It can be a little confusing so let's break it down. Here is the pseudo syntax to help it all make sense:

- If the 3 position of the year is evenly divisible by 2 then A (even), else B (odd).
- A (even): If the 4th/last position of the year is evenly divisible by 4 then it is a leap year, if not it isn't a leap year.
- B (odd): If the last position of the year is either a 2 or a 6 then it is a leap year, if not it isn't a leap year.

Now that wasn't too hard...but it can be difficult and get lost in the formula.

So breaking down the formula to align with this pseudo code can help. We have to explain some of the values in our formula - so let's start with the assumption that

- our date is in cell
**A3**(I also have the $ reference in front of A as this is the column for all of my dates, but I'm going to copy this to multiple rows). - Let's also assume that we want to return the value of "Leap" for a leap year and "No" for any other year...we can then have other cells look for this reference without duplicating all of this logic through out your spreadsheet - one cell in each row for my situation.

**If the 3 position of the year is evenly divisible by 2 then A (even), else B (odd).**

=IF(MOD(MID(YEAR($A3),3,1),2)=0, {EVEN},{ODD})

Now the sub if statements

**A (even): If the 4th/last position of the year is evenly divisible by 4 then it is a leap year, if not it isn't a leap year.**

IF(MOD(MID(YEAR($A3),4,1),4)=0,"Leap","No"

**B (odd): If the last position of the year is either a 2 or a 6 then it is a leap year, if not it isn't a leap year.**

IF(OR(MID(YEAR($A3),4,1)="2",MID(YEAR($A3),4,1)="6"),"Leap","No"

When you put it all together this is what it looks like.

=IF(MOD(MID(YEAR($A3),3,1),2)=0,IF(MOD(MID(YEAR($A3),4,1),4)=0,"Leap","No"),IF(OR(MID(YEAR($A3),4,1)="2",MID(YEAR($A3),4,1)="6"),"Leap","No"))

I hope this helps or gives you ideas for other problems you can solve.

Labels:

4 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2019 10:01 AM

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2019 11:04 AM

Another alternative is:

=IF(DAY(DATE(YEAR(A1),3,0))=29,

“Leap”,”No”)

=IF(DAY(DATE(YEAR(A1),3,0))=29,

“Leap”,”No”)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2019 12:38 PM

@Sergei Baklan Well...if you want to do it the easy way I guess you could do that...doesn't have as good of a story.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2019 01:01 PM

Story is great, but in coding I prefer to keep things simple

Related Conversations

M365 usage analytics > User Activity > Teams> department selected> show data for 12 months?

365Learner
in
Office 365 Adoption Content Pack in PowerBI
on
04-03-2020
71
Views

0 Likes

0 Replies

SharePoint 2013 LIst Filters (by latest date)

dmphil
in
SharePoint
on
02-27-2020
177
Views

0 Likes

0 Replies

Pivot Table StDev calculates different value then the STDEV formula

zsoltturkosi
in
Excel
on
10-21-2019
264
Views

0 Likes

4 Replies

need macro to find and copy range of data between specified cells.

spike3rd
in
Office 365
on
10-09-2019
199
Views

0 Likes

0 Replies

Populating a cell in ref to another cells specific contents (first/last blank cell in sequence)

kiara1992
in
Microsoft Teams
on
08-27-2019
178
Views

0 Likes

0 Replies

Share

Microsoft Store

Education

Developer