DATE functions with fractions of years

[ClaudiaRovastino]

Hi Excel Ninjas,

I am trying to find a date in the past based on a start date (let's say it's today's date) and a duration in years.

I used the DATE function. The function works when the duration is a whole number. When it's not (it has a fraction of a year; 3.25 year for example) the formula doesn't work.


Here is an example:

Start Date Duration Date in past
1/25/2020 -1 1/25/2019
1/25/2020 -2 1/25/2018
1/25/2020 -3 1/25/2017
1/25/2020 -4 1/25/2016
1/25/2020 -3.25 1/25/2016


As you can see, the date I got for a duration of 3.25 years is the same as 4 years.

I wonder if there is a solution to this.

Thank you so much !

[protonLeah]

I don't know what you mean by ...used the date function...
That fⁿ requires three parameters: a year 1900 - 9999, a month 1 - 12 and a day 1 - 31

What formula/function did you actually use?

[ClaudiaRovastino]

here's the function I used:

To calculate the date in the past:
=DATE(YEAR(B48)+C48,MONTH(B48),DAY(B48))

B48: Start Date (01/25/2019)
C48: Duration

Thanks

[Ron Coderre]

With dates in Col_A
and durations in Col_B

Try this regular formula (formatted as a date):

Please Login or Register  to view this content.

Is that something you can work with?

[ClaudiaRovastino]

Yes, thanks a lot!!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.