Add partial year to date

[glyndwr99]

Hello, I'm trying to add 6.75 years to a date in Excel and I think I'
close, but I don't like the 365.26 usage for years..

Here is what I have

=DATE(YEAR(A1)+6, MONTH(A1),DAY(A1) + 273.945 )

Where A1 = 1/1/1960.

And 273.945 = 365.26 *.75 (use 365.26 for average number of days in
year)

But it spits out 10/1/1966... while that may be right, how do I get th
exact date? Thanks

 

[zvkmpw]

Hello, I'm trying to add 6.75 years to a date in Excel and I think I'm

close, but I don't like the 365.26 usage for years..

Here is what I have

=DATE(YEAR(A1)+6, MONTH(A1),DAY(A1) + 273.945 )

Where A1 = 1/1/1960.

And 273.945 = 365.26 *.75 (use 365.26 for average number of days in a
year)

But it spits out 10/1/1966... while that may be right, how do I get the
exact date? Thanks!

I don't know of any official definition of what .75 years is exactly defined to mean. So unless there's such a definition in your context, the approach you're using seems reasonable. I would suggest rounding the result so there aren't hours and minutes included in the value.

Some might define .75 years to mean 9 months, which suggests:
=DATE(YEAR(A1)+6,MONTH(A1)+9,DAY(A1))
However, if A1 is 5/31/19, the result is 3/3/26, which is counter-intuitive..

 

[Ron Rosenfeld]

Hello, I'm trying to add 6.75 years to a date in Excel and I think I'm
close, but I don't like the 365.26 usage for years..

Here is what I have

=DATE(YEAR(A1)+6, MONTH(A1),DAY(A1) + 273.945 )

Where A1 = 1/1/1960.

And 273.945 = 365.26 *.75 (use 365.26 for average number of days in a
year)

But it spits out 10/1/1966... while that may be right, how do I get the
exact date? Thanks!

Since a year can be either 365 or 366 days, 6.75 years is not clearly defined and will vary depending on the included number of leap years in the range. So unless you can precisely define what you mean by 3/4 of a year, your convention of using 365.26 * 0.75 is close, although I would use 365.24 or 365.245 (average number of days/year over 200 years) or 365.25 (average number of days/year over 4 years)

And, if 10/1/1966 is not an "exact date", what do you mean by an "exact date"?