YearFrac and Leap Year.

[Highworld]

Hello,

Was looking for some help on this situation. I am trying to see if there would be a better way for this formula as it is causing issues during leap years.

the Yearfrac is pulling from the following details.

I would be open to changing the formula to work with leapyears going forward but not sure how about to go with that situation.

 

[Highworld]

That is neither a table or a mini xl2bb worksheet? Are there any formulas in this?

I think i got this working.

Book2 (3).xlsx
	G
8	4.793103
Sheet1

Cell Formulas
Range	Formula
G8	G8	=IF(G1=TRUE,LET(ptMths, DATEDIF(I5, I6, "m"), ptDays,I6-EDATE(I5,ptMths),ptMths+(ptDays/DAY(EOMONTH(I6,0)))),IF(D2=TRUE,(G2*12)+(I1-1)+(I2/G3),G2*12))

4.793103

 

[awoohaw]

it is helpful, but you need to select the entire range of cells that are part of the formula. You just pasted one cell.

 

[Highworld]

it is helpful, but you need to select the entire range of cells that are part of the formula. You just pasted one cell.

That was my bad. did not highlight the area.

Book2 (3).xlsx
	D	E	F	G	H	I
1	YTD	2023	2022	TRUE	Month	2
2	FALSE	FALSE	FALSE	0	Day	10
3	$4,197.88	$10,546.25		TRUE	Year	2021
4				TRUE
5				505	Start Date	09/18/23
6				505	Paid Thru	02/10/24
7
8	$4,197.88	$0.00	$0.00	4.793103
9	$875.82			TRUE
10	TRUE
11	$875.82
12
Sheet1

Cell Formulas
Range	Formula
F1	F1	=E1-1
G2	G2	=COUNTIF(E2:F2, TRUE)
G3	G3	=IF(OR(I1=4,I1=6,I1=9,I1=11),30,IF(I1=2,G4,31))
G5	G5	=ROUND(I3/4,0)
G6	G6	=I3/4
D8	D8	=IF(AND(D2=TRUE,I1<>"",I2<>""),SUM(D3:D7),IF(AND(G1=TRUE,I5<>"",I6<>""),SUM(D3:D7),0))
E8:F8	E8	=IF(E2=TRUE,SUM(E3:E7),0)
G8	G8	=IF(G1=TRUE,LET(ptMths, DATEDIF(I5, I6, "m"), ptDays,I6-EDATE(I5,ptMths),ptMths+(ptDays/DAY(EOMONTH(I6,0)))),IF(D2=TRUE,(G2*12)+(I1-1)+(I2/G3),G2*12))
D9	D9	=ROUND(IF(G8=0,0,SUM(D8:F8)/G8),2)
D11	D11	=D9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2	Expression	=$F$10	text	NO
E2	Expression	=$E$10	text	NO
D2	Expression	=$D$10	text	NO
D2	Expression	=$D$10=TRUE	text	NO
E2	Expression	=$E$10=TRUE	text	NO
F2	Expression	=$F$10=TRUE	text	NO
H1:H3,D8	Expression	=$O$9=$P$9	text	NO
H5:I6	Expression	=IF($G$9=FALSE,TRUE,FALSE)	text	NO
H1:I3	Expression	=IF($G$9=TRUE,TRUE,FALSE)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I1	Whole number	between 1 and 12
I2	Whole number	between 1 and 31
I3	Whole number	between 2010 and 2030

 

[awoohaw]

Thanks, and please tell the forum what it is you are trying to calculate? An average of something?
Why are you entering a date in 3 different cells?

 

[Highworld]

Thank you for the help at this point.

What i am trying to get is where the numbers circled in red will help feed into the average circled in Green. For example, calculating the long way would take way too long for the number of times being done in one day. By doing the formula by hand it would come out to 4.77 (rounded). the 4.793103 is currently not calculated in the extra day for the leap year. I am not sure if there is a way to get that to show into the average, as this is causing some errors in data.

 

[awoohaw]

what does the value in G8 mean? Is it the average dollars per day from start date to thru date on the different between one year to the next?
I'm not a financial planner so please tell me what you want the calculation to do? if there are conditions that is fine.

 

[Alex Blakenburg]

So if you provide @awhoohaw with the details he is asking for he can probably provide you more complete response.
For the days calculation, you need to provide the exact steps you take to do the manual calculation including whether you want to include the start and end date or only the end date and what month you are using as the divisor for the fraction of a month calculation.

Your date range does not cross over the end of Feb so the use of the leapyear is only as a divisor for the fraction assuming you use Feb as the base.
The formula I gave you does not include the start day and uses 29 days for Feb as the divisor.
I think you are using 30 days as the divisor (this is the only way I can come up with your number). This means Leap Year does not even factor into this particular example.

If you want the divisor to always be 30 then it simplifies the formula down to.

=LET(ptMths,DATEDIF(I7,I8,"m"),
ptDays,I8-EDATE(I7,ptMths),
ptMths+(ptDays/30))

 

[awoohaw]

So if you provide @awhoohaw with the details he is asking for he can probably provide you more complete response.
For the days calculation, you need to provide the exact steps you take to do the manual calculation including whether you want to include the start and end date or only the end date and what month you are using as the divisor for the fraction of a month calculation.

Your date range does not cross over the end of Feb so the use of the leapyear is only as a divisor for the fraction assuming you use Feb as the base.
The formula I gave you does not include the start day and uses 29 days for Feb as the divisor.
I think you are using 30 days as the divisor (this is the only way I can come up with your number). This means Leap Year does not even factor into this particular example.

View attachment 108455

If you want the divisor to always be 30 then it simplifies the formula down to.

=LET(ptMths,DATEDIF(I7,I8,"m"),
ptDays,I8-EDATE(I7,ptMths),
ptMths+(ptDays/30))

@Alex Blakenburg , i think you have it figured out much more than me. But, @Highworld , if you can add anything that can help us get to your solution it would be great.