Help

Formula to sum years passed since a listed date

Topic Labels: Formulas
Solved
Jump to Solution
1644 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Bantu_Design_Re
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi all,
Is there a way to sum the number of years passed (as a whole number, rounded down) from a listed date to present? I’m using this for an asset register. There are 4 important columns:
A) date of purchase
B) original purchase value
C) annual depreciation (as a %)
D) current value

I’m looking to write a formula for current value (D) that will multiply the annual deprectiation % © by the number of years that has passed since the date of purchase (years since A), then multiply that by the original purchase value (B).

For example, if 3 years have passed since purchase date, the original price purchase value was $1,000, and the depreciation is 16.67%, it would be ((3*.1667)*1,000), returning a current value of $500.10. How do I get that 3 to caclucate automatically in a formula?

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

What you want is the DATETIME_DIFF() function. Just taking a stab at your field names, it might look something like this:

DATETIME_DIFF(TODAY(), {Purchase Date}, "years") * Depreciation * {Purchase Value}

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

What you want is the DATETIME_DIFF() function. Just taking a stab at your field names, it might look something like this:

DATETIME_DIFF(TODAY(), {Purchase Date}, "years") * Depreciation * {Purchase Value}
Bantu_Design_Re
5 - Automation Enthusiast
5 - Automation Enthusiast

That worked, thanks!