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.

 

[awoohaw]

what kind of values are in cells: I35, I34, D31,G31,I30,I31,G32,G31? A mini table or xl2bb min worksheet (link below) would be very helpful.

 

[Highworld]

 

[awoohaw]

View attachment 108245

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

 

[Highworld]

That is neither a table or a mini xl2bb worksheet? Are there any formula

I am trying to post it but my computer is not liking the XL2BB program install. Not sure if this will work this way as there are formulas within the table.

	Employer			Pay Period Ending
			​ ​ YTD	2023	2022	TRUE	Month	2​
	Uncheck box if not using to qualify		TRUE	TRUE	FALSE	1	Day	10​
	Gross Wages					28	Year	2021​
	Overtime					28
	Commission					505	Start Date	#######​
	Bonus					505	Paid Thru	#######​
	Other
	Total Qualifying from Paystub/VOE		$0.00	$0.00	$0.00	4.733333
	Monthly		$0.00
						TRUE​
			TRUE​	TRUE​

 

[awoohaw]

I understand that add ins can be disallowed on work computers. This helps. Can copy and paste the formulas and tell us what cells they are in? But, this may be enough for some people to try to get you a solution?
Thanks.

 

[Highworld]

I understand that add ins can be disallowed on work computers. This helps. Can copy and paste the formulas and tell us what cells they are in? But, this may be enough for some people to try to get you a solution?
Thanks.

This is the formula in question that I am having issues with. It is located in cell G10 from the attached screenshot.
=IF(G3=TRUE,YEARFRAC(I8,I7)*12,IF(D4=TRUE,(G4*12)+(I3-1)+(I4/G5),G4*12))

 

[awoohaw]

I think you have something misaligned. The cells colored PINK are in your formula and have no value in the worksheet.
Please give (type cellref: Value, for each value) data for inputs, and an expected outcome in cell G10.
Somehting like
G3: ###
G4: ###
G5: ###
I3: ##
I4: ##

Expect: G10: #####

Or maybe you can tell us just what is it you are trying to calculate?

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		Employer:
3				YTD	2023	2022		Month
4		If Not Using to Qualify		TRUE	TRUE	FALSE		Day
5		Gross Wages						Year
6		Overtime							Started after 1/1
7		Commission						Start Date	2023-09-18
8		Bonus						Paid Thru	2024-02-10
9		Other
10		Earning from PS/VOE		$ 0.00	$ 0.00	$ 0.00	#DIV/0!
11		Monthly
12
Sheet1

Cell Formulas
Range	Formula
D4:E4	D4	=TRUE()
F4	F4	=FALSE()
D10:F10	D10	=SUM(D4:D9)
G10	G10	=IF(G3=TRUE,YEARFRAC(I7,I8)*12,IF(D4=TRUE,(G4*12)+(I3-1)+(I4/G5),G4*12))

 

[Peter_SSs]

my computer is not liking the XL2BB program install.

Have a look through this thread to see if the suggestions help.

 

[Alex Blakenburg]

Would something like this work for you ?
(I have taken the fraction of the month to be based on the End Month)
You would need to add your conditions if the principle works.

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