Excel Leap year formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2160064%22%20slang%3D%22en-US%22%3EExcel%20Leap%20year%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2160064%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20vacation%20spreadsheet%20and%20I%20have%20come%20across%20a%20glitch%20that%20has%20to%20do%20with%20leap%20year%2C%26nbsp%3B%20I%20need%20to%20add%20a%20leap%20year%20formula%20to%20an%20existing%20formula%20so%20it%20can%20calculate%20my%20vacation%20correctly%20because%20the%20longer%20the%20person%20the%20formula%20calculates%20wrong%20of%20course%20because%20we%20did%20not%20include%20a%20leap%20year%20formula%3C%2FP%3E%3CP%3EIf%20i%20attach%20a%20line%20of%20the%20spreadsheet%20can%20any%20one%20help%20me%20on%20the%20formula%20and%20placement%20of%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2160064%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2160072%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Leap%20year%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2160072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F976317%22%20target%3D%22_blank%22%3E%40lperry40%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22994%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22122%22%3E%3DROUNDDOWN(I168-F168%2C0)%3C%2FTD%3E%3CTD%20width%3D%22112%22%3E%3D(DATEDIF(D168%2CE168%2C%22d%22)%2F365)%3C%2FTD%3E%3CTD%20width%3D%22122%22%3E%3DROUNDDOWN(H168%2C0)%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E20%3C%2FTD%3E%3CTD%20width%3D%22138%22%3E10%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%3DSUM(J168%2BK168)%3C%2FTD%3E%3CTD%20width%3D%22116%22%3E9%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E21%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a vacation spreadsheet and I have come across a glitch that has to do with leap year,  I need to add a leap year formula to an existing formula so it can calculate my vacation correctly because the longer the person the formula calculates wrong of course because we did not include a leap year formula

If i attach a line of the spreadsheet can any one help me on the formula and placement of it?

 

 

8 Replies

@lperry40 

=ROUNDDOWN(I168-F168,0)=(DATEDIF(D168,E168,"d")/365)=ROUNDDOWN(H168,0)2010=SUM(J168+K168)921

@lperry40 

The basis of check could be the fact if DAY() for 29th of Feb returns 29 or 1, like

=IF(DAY(DATE(2021,2,29))=1, "not leap", "leap")

 

@Sergei Baklan 

 

so how do I incorporate it into my existing formula? because this is the cell that the days start to go wrong (see below) and the longer the person is employed (for example this gentleman has been at our company for 30 years and he is about 5 to 6 days off because of the leap years.)  

 

=(DATEDIF(D168,E168,"d")/365

 

=(DATEDIF(D168,E168,"d")/365  ????  my guess is 

=(DATEDIF(D168,E168,"d")/365)*(IF(DAY(DATE(2021,2,29))=1  

 

I can send you the whole spreadsheet if you like so you can have a complete picture of what we are doing

@lperry40 

Could you please explain the business logic what is behind. You basis formula is 

=DATEDIF(D1,E1,"d")/365

Let assume in E1 is Dec 25, 2020 (leap year) and in E1 is Jan 10, 2021 (not leap year). Formula returns 0.04386, how the leap or not leap shall affect the logic of above calculations?

 

What if start date is Dec 25, 1999 and we have few leap years in the range?

 

As a comment, formulas

=(E1-D1)/365

or

=YEARFRAC(D1,E1,1)

return exactly the same result as your one.

line 168 is off because we have not taken into consideration leap years in our formula so the longer a person is with us the more the vaca days calculate incorrect

@lperry40 

Thank you for the file, but it doesn't explain how and which exactly calculation depends on leap / not leap year.

 

In my country each person has right on certain amount of calendar days of vacation in each work year, and it doesn't depend on how many years the person worked, there were leap years or not.

 

I have no idea what is the logic for vacation calculation in your case, and in general that's not my business. I only need to know the logic of concrete formula, what depends on what and where are leaps year here.