Forum Discussion

Copper Contributor

Feb 23, 2021

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

Diamond Contributor

Feb 26, 2021

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

lperry40
Copper Contributor
Feb 26, 2021
I can send you the whole spreadsheet if you like so you can have a complete picture of what we are doing
- SergeiBaklan
  Diamond Contributor
  Feb 26, 2021
  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.
  - lperry40
    Copper Contributor
    Feb 26, 2021
    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
Copper Contributor
Feb 26, 2021
SergeiBaklan

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

Resources