Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Add partial year to date
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add partial year to date
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.. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add partial year to date
On Mon, 21 May 2012 14:05:47 +0000, glyndwr99 wrote:
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"? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Month and year from Cell containing date, month, year & time | Excel Worksheet Functions | |||
Determine year over year date for comparison | Excel Worksheet Functions | |||
Partial Year Adjustments (Mod Function) | Excel Worksheet Functions | |||
How do you compute partial year depreciation using DDB, SYD, SLN,V | Excel Worksheet Functions | |||
Year-to-date year to date formula | Excel Worksheet Functions |