Leap Year Issue

[FuNeS13]

This is one of the most stressful reports I have ever helped with and I am out of ideas... I have the following Formula:

=($C$3/(($E$3+365)-$E$3))*DAY(EOMONTH($E$3,$T11))

Where:

C3 = 80,000
E3 = 2/1/2024
T11 = 0 (it goes down row by row and adds 1 each row).

Basically what it does it divides the 80,000 by the number of days in a year (365) and then multiplies the result for the number of days in the first month, in this case february (2024).

As you can imagine the formula works great when I put an early non leap year date (for example 2/1/2021) and it some cases other months as well... If my date is 3/1/2023 the formula add an aditional day to the total sum which is located in P23 and it should match the amount in C3...

I'm out of ideas and I have no idea how to make this work for leap year dates as well...

has anymore encountered anything like this?

 

[DRSteele]

Your problem is that static 365 in the equation. The 365 has to be 366 when the next Feb in the list of twelve months (i.e., P11 to P22, I surmise) is one with 29 days. I concocted this formula to do that. It looks like the dog's breakfast, but it works.

Put this in P11 instead.

=$C$3/IF(DAY(EOMONTH(IF(MONTH($E$3)>2,DATE(YEAR($E$3)+1,2,1),DATE(YEAR($E$3),2,1)),0))=29,366,365)*DAY(EOMONTH($E$3,$T11))

 

[Rick Rothstein]

Maybe this modification of your formula will produce the results you want...

=($C$3/(($E$3+365+(MONTH(DATE(YEAR($E$3),2,29))=2))-$E$3))*DAY(EOMONTH($E$3,$T11))

What I added in red will add 1 to the 365 if the month number for February 29th is 2 (it will be 3 otherwise) which would be the case for a leap year. The EOMONTH function is smart enough to handle leap years so nothing needs to be done with it.

 

[FuNeS13]

Thank you DRSteele & Rick Rothstein, both formulas still give me a number different than the total...

 

[Dave Patton]

I am not clear on your complete challenge. The allocation shown below may help.
The months are dates like Feb 1 2024 formatted to show months-years.

T202003a.xlsm
	A	B	C	D	E	F	G	H
1								Allocate to months
2	Start	End	Amount	Total	Feb-24	Mar-24	Apr-24	May-24
3	01-Feb-24	31-Dec-24	80,000.00	335	6,925.37	7,402.99	7,164.18	7,402.99
4	15-Feb-24	31-May-24	1,000.00	107	140.19	289.72	280.37	289.72
1dd

Cell Formulas
Range	Formula
D3:D4	D3	=B3-A3+1
E3:H4	E3	=MAX(0, MIN(EOMONTH(E$2, 0), $B3) + 1 - MAX(E$2, $A3))*$C3/$D3

 

[DRSteele]

It seems to work for me.

MrExcel posts18.xlsx
	C	D	E	F	G	H	I						O	P				T
2				next Feb	Days in Next Feb	days to use
3	80000		3/1/2023	2/1/2024	29	366
4
5
6
7
8
9
10													218.5792
11													31	6775.956				0
12													30	6557.377				1
13													31	6775.956				2
14													30	6557.377				3
15													31	6775.956				4
16													31	6775.956				5
17													30	6557.377				6
18													31	6775.956				7
19													30	6557.377				8
20													31	6775.956				9
21													31	6775.956				10
22													29	6338.798				11
23														80000
Sheet8

Cell Formulas
Range	Formula
F3	F3	=IF(MONTH(E3)>2,DATE(YEAR(E3)+1,2,1),DATE(YEAR(E3),2,1))
G3	G3	=DAY(EOMONTH(IF(MONTH(E3)>2,DATE(YEAR(E3)+1,2,1),DATE(YEAR(E3),2,1)),0))
H3	H3	=IF(DAY(EOMONTH(IF(MONTH(E3)>2,DATE(YEAR(E3)+1,2,1),DATE(YEAR(E3),2,1)),0))=29,366,365)
O10	O10	=$C$3/IF(DAY(EOMONTH(IF(MONTH($E$3)>2,DATE(YEAR($E$3)+1,2,1),DATE(YEAR($E$3),2,1)),0))=29,366,365)
O11:O22	O11	=DAY(EOMONTH($E$3,$T11))
T12:T22	T12	=T11+1
P11:P22	P11	=$C$3/IF(DAY(EOMONTH(IF(MONTH($E$3)>2,DATE(YEAR($E$3)+1,2,1),DATE(YEAR($E$3),2,1)),0))=29,366,365)*DAY(EOMONTH($E$3,$T11))
P23	P23	=SUM(P11:P22)

 

[FuNeS13]

It seems to work for me.

MrExcel posts18.xlsx
	C	D	E	F	G	H	I						O	P				T
2				next Feb	Days in Next Feb	days to use
3	80000		3/1/2023	2/1/2024	29	366
4
5
6
7
8
9
10													218.5792
11													31	6775.956				0
12													30	6557.377				1
13													31	6775.956				2
14													30	6557.377				3
15													31	6775.956				4
16													31	6775.956				5
17													30	6557.377				6
18													31	6775.956				7
19													30	6557.377				8
20													31	6775.956				9
21													31	6775.956				10
22													29	6338.798				11
23														80000
Sheet8

Cell Formulas
Range	Formula
F3	F3	=IF(MONTH(E3)>2,DATE(YEAR(E3)+1,2,1),DATE(YEAR(E3),2,1))
G3	G3	=DAY(EOMONTH(IF(MONTH(E3)>2,DATE(YEAR(E3)+1,2,1),DATE(YEAR(E3),2,1)),0))
H3	H3	=IF(DAY(EOMONTH(IF(MONTH(E3)>2,DATE(YEAR(E3)+1,2,1),DATE(YEAR(E3),2,1)),0))=29,366,365)
O10	O10	=$C$3/IF(DAY(EOMONTH(IF(MONTH($E$3)>2,DATE(YEAR($E$3)+1,2,1),DATE(YEAR($E$3),2,1)),0))=29,366,365)
O11:O22	O11	=DAY(EOMONTH($E$3,$T11))
T12:T22	T12	=T11+1
P11:P22	P11	=$C$3/IF(DAY(EOMONTH(IF(MONTH($E$3)>2,DATE(YEAR($E$3)+1,2,1),DATE(YEAR($E$3),2,1)),0))=29,366,365)*DAY(EOMONTH($E$3,$T11))
P23	P23	=SUM(P11:P22)

thank you!!!