Date Calculation with Year's decimals

[mba_110]

Hi everyone,

I hope all are fine.

I need your help in below sheet on formula cell I11 and J12, some formulas i have already completed but these two formulas are not giving me the desired results.

Fixed Assets - Register.xlsx
	B	C	D	E	F	G	H	I	J	K
7
8	S.NO	ASSET NO	PURCHASE DETAILS		ASSET NAME	LIFE (Years)	ALLOCATION PERIOD		AMOUNT	BALANCE
9			DATE	AMOUNT			START	END
10
11	1	LT-FA0001	15 Jan 2025	AED 5,000.00	KET	1.0		15 Jan 2026		AED 5,000.00
12					KET		15 Jan 2025	31 Jan 2025	222.22	AED 4,777.78
13					KET		1 Feb 2025	28 Feb 2025	375.00	AED 4,402.78
14					KET		1 Mar 2025	31 Mar 2025	416.67	AED 3,986.11
15					KET		1 Apr 2025	30 Apr 2025	402.78	AED 3,583.33
16					KET		1 May 2025	31 May 2025	416.67	AED 3,166.67
17							1 Jun 2025	30 Jun 2025	402.78	AED 2,763.89
18							1 Jul 2025	31 Jul 2025	416.67	AED 2,347.22
19							1 Aug 2025	31 Aug 2025	416.67	AED 1,930.56
20							1 Sep 2025	30 Sep 2025	402.78	AED 1,527.78
21							1 Oct 2025	31 Oct 2025	416.67	AED 1,111.11
22							1 Nov 2025	30 Nov 2025	402.78	AED 708.33
23							1 Dec 2025	31 Dec 2025	416.67	AED 291.67
24							1 Jan 2026	15 Jan 2026	194.44	AED 97.22
25
26				AED 5,000.00	TOTAL AMOUNT :				4,902.78	AED 97.22
27
Asset Depriciation

Cell Formulas
Range	Formula
E11	E11	=VLOOKUP(C11,'Asset List'!$C$11:$N$131,3,0)
I11	I11	=DATE(YEAR(D11)+G11,MONTH(D11),DAY(D11))
K11	K11	=E11
H12	H12	=D11
I12:I24	I12	=IF(H12+30<$I$11,EOMONTH(H12,0),$I$11)
J12:J24	J12	=IF(I12-H12>=30,($E$11/$G$11)/12,($E$11/$G$11)/12/30*(I12-H12))
K12:K24	K12	=K11-J12
H13:H24	H13	=IF($I$11>EOMONTH(I12,0),I12+1,"")
F11	F11	=VLOOKUP(C11,'Asset List'!$C$11:$N$131,4,0)
F12:F16	F12	=F11
E26,J26	E26	=SUBTOTAL(9,E11:E25)
K26	K26	=K11-J26

G11 is a manual entry cell where i want to put the year value in single or double decimals which will effect the calculation in above sheet for the End period at cell number I11, secondly i have formula in J12 which will calculate the amount of depreciation on each particular line (from start date to end date).

* I11 - i want the End date mentioned on I11 should calculate correctly when i use decimals in G11 for year(s).
** J12 - i want the formula in J12 should allocate the correct amount on each line with their respective period (days) from start to end, also when the period ends the total of J26 should match with E11, their should not be any difference or balance remain.

I really appreciate your help and support.

 

[footoo]

For the formula in I11 : =D11+G11*365

 

[mba_110]

Thanks for your help what about J12 formula ?

 

[footoo]

Thanks for your help what about J12 formula ?

=$E$11/($G$11*365)*(I11-H11+1)

The date in I24 should be 14 Jan 2026.

 

[mba_110]

=$E$11/($G$11*365)*(I11-H11+1)

The date in I24 should be 14 Jan 2026.

This formula is not correct, consider start date D11, anyway this formula produce wrong result.

 

[footoo]

This formula is not correct, consider start date D11, anyway this formula produce wrong result.

Sorry, the row numbers should be 12, not 11.
=$E$11/($G$11*365)*(I12-H12+1)

 

[mba_110]

Sorry, the row numbers should be 12, not 11.
=$E$11/($G$11*365)*(I12-H12+1)

Still not correct

 

[footoo]

Still not correct

Not correct in what way? It works for me.

 

[mba_110]

** J12 - i want the formula in J12 should allocate the correct amount on each line with their respective period (days) from start to end, also when the period ends the total of J26 should match with E11, their should not be any difference or balance remain.

 

[footoo]

** J12 - i want the formula in J12 should allocate the correct amount on each line with their respective period (days) from start to end, also when the period ends the total of J26 should match with E11, their should not be any difference or balance remain.

It works for me in the way you describe.
Did you enter this in J12 : =$E$11/($G$11*365)*(I12-H12+1)
What result is J12 returning?