Forum Discussion
Excel Leap year formula
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
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")- I can send you the whole spreadsheet if you like so you can have a complete picture of what we are doing
Could you please explain the business logic what is behind. You basis formula is
=DATEDIF(D1,E1,"d")/365Let 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.
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
