Date Calculation with Year's decimals

M

mba_110

Board Regular
Joined
Nov 28, 2012
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJK
7
8S.NOASSET NOPURCHASE DETAILSASSET NAMELIFE (Years)ALLOCATION PERIODAMOUNT BALANCE
9DATE AMOUNT STARTEND
10
111LT-FA000115 Jan 2025AED 5,000.00KET1.015 Jan 2026AED 5,000.00
12KET15 Jan 202531 Jan 2025222.22AED 4,777.78
13KET1 Feb 202528 Feb 2025375.00AED 4,402.78
14KET1 Mar 202531 Mar 2025416.67AED 3,986.11
15KET1 Apr 202530 Apr 2025402.78AED 3,583.33
16KET1 May 202531 May 2025416.67AED 3,166.67
171 Jun 202530 Jun 2025402.78AED 2,763.89
181 Jul 202531 Jul 2025416.67AED 2,347.22
191 Aug 202531 Aug 2025416.67AED 1,930.56
201 Sep 202530 Sep 2025402.78AED 1,527.78
211 Oct 202531 Oct 2025416.67AED 1,111.11
221 Nov 202530 Nov 2025402.78AED 708.33
231 Dec 202531 Dec 2025416.67AED 291.67
241 Jan 202615 Jan 2026194.44AED 97.22
25
26AED 5,000.00TOTAL AMOUNT :4,902.78AED 97.22
27
Asset Depriciation
Cell Formulas
RangeFormula
E11E11=VLOOKUP(C11,'Asset List'!$C$11:$N$131,3,0)
I11I11=DATE(YEAR(D11)+G11,MONTH(D11),DAY(D11))
K11K11=E11
H12H12=D11
I12:I24I12=IF(H12+30<$I$11,EOMONTH(H12,0),$I$11)
J12:J24J12=IF(I12-H12>=30,($E$11/$G$11)/12,($E$11/$G$11)/12/30*(I12-H12))
K12:K24K12=K11-J12
H13:H24H13=IF($I$11>EOMONTH(I12,0),I12+1,"")
F11F11=VLOOKUP(C11,'Asset List'!$C$11:$N$131,4,0)
F12:F16F12=F11
E26,J26E26=SUBTOTAL(9,E11:E25)
K26K26=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.
 
Sort by date Sort by votes
footoo said:
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?
Click to expand...
I shared the image with formula on J12, but the amount of total periods and E11 is not matching at the end of the period in last row it should be zero. which is happening.
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I've just seen your data in post #9.
What formula is in I11?
The date should be 30 Jun 2026, not 2 Jul 2026.
 
Upvote 0
footoo said:
I've just seen your data in post #9.
What formula is in I11?
The date should be 30 Jun 2026, not 2 Jul 2026.
Click to expand...
Fixed Assets - Register.xlsx
ABCDEFGHIJK
10
111LT-FA00011 Jan 20255,000.00KET1.502 Jul 2026AED 5,000.00
12KET1 Jan 202531 Jan 2025283.11AED 4,716.89
13KET1 Feb 202528 Feb 2025255.71AED 4,461.19
14KET1 Mar 202531 Mar 2025283.11AED 4,178.08
15KET1 Apr 202530 Apr 2025273.97AED 3,904.11
16KET1 May 202531 May 2025283.11AED 3,621.00
171 Jun 202530 Jun 2025273.97AED 3,347.03
181 Jul 202531 Jul 2025283.11AED 3,063.93
191 Aug 202531 Aug 2025283.11AED 2,780.82
201 Sep 202530 Sep 2025273.97AED 2,506.85
211 Oct 202531 Oct 2025283.11AED 2,223.74
221 Nov 202530 Nov 2025273.97AED 1,949.77
231 Dec 202531 Dec 2025283.11AED 1,666.67
241 Jan 202631 Jan 2026283.11AED 1,383.56
251 Feb 202628 Feb 2026255.71AED 1,127.85
261 Mar 202631 Mar 2026283.11AED 844.75
271 Apr 202630 Apr 2026277.78AED 566.97
281 May 202631 May 2026277.78AED 289.19
291 Jun 202630 Jun 2026277.78AED 11.42
301 Jul 20262 Jul 202623.15-AED 11.73
31
32AED 5,000.00TOTAL AMOUNT :5,011.73-AED 11.73
33
Asset Depriciation
Cell Formulas
RangeFormula
E11E11=VLOOKUP(C11,'Asset List'!$C$12:$N$131,3,0)
I11I11=D11+G11*365
K11K11=E11
H12H12=D11
I12:I29I12=IF(H12+30<=$I$11,EOMONTH(H12,0),IF(H12="","",$I$11))
J12:J26J12=$E$11/($G$11*365)*(I12-H12+1)
K12:K30K12=K11-J12
H13:H30H13=IF($I$11>EOMONTH(I12,0),I12+1,"")
J27:J29J27=IF(I27-H27+1>=30,($E$11/$G$11)/12,($E$11/$G$11)/12/30*(I27-H27+1))
I30I30=IF(H30+30<$I$11,EOMONTH(H30,0),IF(H30="","",$I$11))
J30J30=IF(I30-H30>=30,($E$11/$G$11)/12,($E$11/$G$11)/12/30*(I30-H30+1))
F11F11=VLOOKUP(C11,'Asset List'!$C$12:$N$131,4,0)
F12:F16F12=F11
E32,J32E32=SUBTOTAL(9,E11:E31)
K32K32=K11-J32
 
Upvote 0
I see what's happened.
Is the life of the asset always complete months?
If so, use this in I11 : =EDATE(D11,G11*12)-1
 
Upvote 0
Life of asset is limited to End Date not limited to end of month.

Please take care of decimals of G11, your formula is not correct for I11 and J12 it is not considering the decimals of G11 that's the reason it also having balance at the end in total.

I am expecting result at bottom same total of E11 at the end of last line of period i should be zero in balance column, i hope i make it clear to understand.

Also you have to produce correct end date at I11 formula, again considering the G11 decimals.
 
Upvote 0
mba_110 said:
Life of asset is limited to End Date not limited to end of month.
Click to expand...
My question was : Is it always complete months from the start date?
mba_110 said:
Please take care of decimals of G11, your formula is not correct for I11 and J12 it is not considering the decimals of G11 that's the reason it also having balance at the end in total.

I am expecting result at bottom same total of E11 at the end of last line of period i should be zero in balance column, i hope i make it clear to understand.

Also you have to produce correct end date at I11 formula, again considering the G11 decimals.
Click to expand...
Did you try the revised formula in I11?

Everything works for me assuming complete months from the start date.
 
Upvote 0
footoo said:
My question was : Is it always complete months from the start date?

Did you try the revised formula in I11?

Everything works for me assuming complete months from the start date.
Click to expand...
Each line of start and end is one month from starting date which is at D11, but at the end it should stop at date mentioned at I11 weather its one day or week or month it should stop on this date.

or example

if D11 is 1 Jan 2024 and I11 is 15 Feb 2024 then

01 Jan 2024 31 Jan 2024
01 Feb 2024 15 Feb 2024 - Should stop here and the balance should be zero.

Again each line is one month until the end date is reached once its reached it should cut off at same date, no need to go at end of the month.

if all formula working correct with you then send me the XL2bb here.
 
Upvote 0
mba_110 said:
Each line of start and end is one month from starting date which is at D11, but at the end it should stop at date mentioned at I11 weather its one day or week or month it should stop on this date.

or example

if D11 is 1 Jan 2024 and I11 is 15 Feb 2024 then

01 Jan 2024 31 Jan 2024
01 Feb 2024 15 Feb 2024 - Should stop here and the balance should be zero.

Again each line is one month until the end date is reached once its reached it should cut off at same date, no need to go at end of the month.

if all formula working correct with you then send me the XL2bb here.
Click to expand...
If the life of the asset is not in complete months from the start date, it is a problem that would probably involve revision of your worksheet to account for daily depreciation.
Do you really need fractions of a month?
Using fractions of a year is not ideal - it will result in differences in the total if the year fraction does not represent complete months.
Also, leap years are likely to be a problem.

I could not download your data. Can you send by XL2bb again?
 
Upvote 0
Workbook1.xlsm
DEFGHIJ
1115 Jan 202550001.514 Jul 20269.157509158
1215 Jan 202531 Jan 2025155.67766
131 Feb 202528 Feb 2025256.41026
141 Mar 202531 Mar 2025283.88278
151 Apr 202530 Apr 2025274.72527
161 May 202531 May 2025283.88278
171 Jun 202530 Jun 2025274.72527
181 Jul 202531 Jul 2025283.88278
191 Aug 202531 Aug 2025283.88278
201 Sep 202530 Sep 2025274.72527
211 Oct 202531 Oct 2025283.88278
221 Nov 202530 Nov 2025274.72527
231 Dec 202531 Dec 2025283.88278
241 Jan 202631 Jan 2026283.88278
251 Feb 202628 Feb 2026256.41026
261 Mar 202631 Mar 2026283.88278
271 Apr 202630 Apr 2026274.72527
281 May 202631 May 2026283.88278
291 Jun 202630 Jun 2026274.72527
301 Jul 202614 07 2026128.20513
315000
Sheet1
Cell Formulas
RangeFormula
I11I11=EDATE(D11,G11*12)-1
J11J11=E11/(I11-D11+1)
J12:J30J12=$J$11*(I12-H12+1)
J31J31=SUM(J12:J30)


I have put the daily depreciation in J11 and revised the formal in J12 and down.
My previous comments about complete months still apply.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA to automate calculation
Replies
1
Views
103
HighAndWilder
H
M
  • Question Question
COUNTIFS formula for number of dates in a quarter??
Replies
4
Views
2K
Alex Blakenburg
A
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
B
  • Question Question
calculate monthly value
Replies
0
Views
384
BrutalLogiC
B
J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
72
HighAndWilder
H

Forum statistics

Threads
1,225,968
Messages
6,188,106
Members
453,460
Latest member
Cjohnson3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top