Calculate time elapsed Excel

1
votes

I would like to calculate the number of months a certain object has aged in 2016.

Background:

This is a depreciation recalculation for accounting purposes within excel. I have a large list of assets that have a ‘start date’ and ‘end date’. I would like to calculate the number of months have elapsed for that asset in 2016.

The problem:

Assets are constantly being added to the list, so you have many different start and end dates. Some assets start in 2016, some assets end in 2016. I cannot determine a formula that will simply calculate the # of months that asset has in 2016.

Example issue:

I have an asset with a start date of April 1st, 2016. I have an asset with a an end date of April 30, 2016.

Is there a formula for how much time has that asset spent in the year 2016?

1
excel

1 Answers

1
votes

How about the DATEDIF function - with your start/end date in cell A1:

=DATEDIF("1/1/2016",A1,"m")

This is an old function which you cannot find in Excel's list of functions, but you can read more here

Note that this will give you an error for dates in A1 that are before 1/1/2016 so you might want to wrap IFERROR around the formula like so:

=IFERROR(DATEDIF("1/1/2016",A1,"m"),0)