Feb 23 2021 11:17 AM
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?
Feb 23 2021 11:21 AM
=ROUNDDOWN(I168-F168,0) | =(DATEDIF(D168,E168,"d")/365) | =ROUNDDOWN(H168,0) | 20 | 10 | =SUM(J168+K168) | 9 | 21 |
Feb 26 2021 11:49 AM
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")
Feb 26 2021 12:05 PM
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
|
Feb 26 2021 12:20 PM
Feb 26 2021 01:11 PM
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.
Feb 26 2021 01:49 PM
Feb 27 2021 02:43 AM
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.