Date Function

  • Thread starter Thread starter Grandmother
  • Start date Start date
G

Grandmother

I need help with the following. I cannot get the correct formula. I a
using Excel 2002.

Calculate the Years of doing business with each person by subtractin
thre Start Date (12/23/89) which is in G2, from 8/14/2003. Use 365.2
for the number of days in a year. Use an integer to represent th
number of complete years. Do not include partial years, and do no
round up. Any decimal positions are truncated
 
Hi Grandmother!

With, for example a date in H2 and your start date in G2

Use:
=DATEDIF($G$2,H2,"y")

Since your Start Date is a constant, I've absolutely referenced G2.

For more detail on DATEDIF see:

http://www.cpearson.com/excel/datedif.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Grandmother

=INT(YEARFRAC($G$2,H2))
(You need to have the Analysis ToolPak addin loaded.)
--
XL2002
Regards

William

(e-mail address removed)

| I need help with the following. I cannot get the correct formula. I am
| using Excel 2002.
|
| Calculate the Years of doing business with each person by subtracting
| thre Start Date (12/23/89) which is in G2, from 8/14/2003. Use 365.25
| for the number of days in a year. Use an integer to represent the
| number of complete years. Do not include partial years, and do not
| round up. Any decimal positions are truncated.
|
|
| ---
| Message posted
|
 
Hi William!

Although using YEARFRAC with the third argument of 1 should work,
you'll find that you get problems with using YEARFRAC and that it
produces errors where the dates are more than one year apart.

This function was intended for calculating time spans of less than one
year (note the name), and is more specifically intended for things
like financial calculations of accrued interest,etc where different
day count bases may be used. Without the 3rd argument, you are
assuming 30 day months and 360 days per year. Even setting 3rd
argument to 1, you'll get errors when the 2nd date is very close to
the anniversary of the birthdate. And you get different results for
different years. In some instances on the exact anniversary you'll
find non-integer results

Stick to DATEDIF for this purpose. If you need the non-integer
fractions of a year, I'm afraid it back to more complicated formulas:

=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY
(A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEA
R(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))


Nasty? Yes! But it's accurate according to most people's understanding
/ definition of how to calculate a fraction of a year. There's a
fuller outline of the problem at:

http://tinyurl.com/2nstc

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Interesting function. I almost have the concept of extending it to find out
how many months and then days.
for example:
columnA columnB columnC columnD
r1 1/15/2001 =today() =datedifA1,B1,"y")
= 3 years
in columnD =datedif(A1,B1,"m")-(C1*12)
= 38 months - 36 = 2 months

However, will this be accurate at all times? As I believe the months are
30.4375 days in this calculation {365.25/12}.

And in [columnE] I believe that days would then be:
=DATEDIF(A1,B1,"d")-(C1*365.25) = 66.25 total days beyond the years
determined in C1
extending it to subtract the 2 months would be:
=DATEDIF(A1,B1,"d")-(C1*365.25)-((365.25/12)*D1) = 5.375 days

Norman Harker said:
Hi Grandmother!

With, for example a date in H2 and your start date in G2

Use:
=DATEDIF($G$2,H2,"y")

Since your Start Date is a constant, I've absolutely referenced G2.

For more detail on DATEDIF see:

http://www.cpearson.com/excel/datedif.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Click to expand...
 
Hi Norman

You are right. Apologies to the OP and thanks for the detailed explanation.

--
XL2002
Regards

William

(e-mail address removed)

| Hi William!
|
| Although using YEARFRAC with the third argument of 1 should work,
| you'll find that you get problems with using YEARFRAC and that it
| produces errors where the dates are more than one year apart.
|
| This function was intended for calculating time spans of less than one
| year (note the name), and is more specifically intended for things
| like financial calculations of accrued interest,etc where different
| day count bases may be used. Without the 3rd argument, you are
| assuming 30 day months and 360 days per year. Even setting 3rd
| argument to 1, you'll get errors when the 2nd date is very close to
| the anniversary of the birthdate. And you get different results for
| different years. In some instances on the exact anniversary you'll
| find non-integer results
|
| Stick to DATEDIF for this purpose. If you need the non-integer
| fractions of a year, I'm afraid it back to more complicated formulas:
|
| =DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY
| (A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEA
| R(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))
|
|
| Nasty? Yes! But it's accurate according to most people's understanding
| / definition of how to calculate a fraction of a year. There's a
| fuller outline of the problem at:
|
| http://tinyurl.com/2nstc
|
| --
| Regards
| Norman Harker MVP (Excel)
| Sydney, Australia
| (e-mail address removed)
| Excel and Word Function Lists (Classifications, Syntax and Arguments)
| available free to good homes.
| | > Hi Grandmother
| >
| > =INT(YEARFRAC($G$2,H2))
| > (You need to have the Analysis ToolPak addin loaded.)
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > (e-mail address removed)
| >
| message
| > | > | I need help with the following. I cannot get the correct formula.
| I am
| > | using Excel 2002.
| > |
| > | Calculate the Years of doing business with each person by
| subtracting
| > | thre Start Date (12/23/89) which is in G2, from 8/14/2003. Use
| 365.25
| > | for the number of days in a year. Use an integer to represent the
| > | number of complete years. Do not include partial years, and do
| not
| > | round up. Any decimal positions are truncated.
| > |
| > |
| > | ---
| > | Message posted
| > |
| >
| >
| >
|
|
 
Hi William!

No need for apologies. I think it correct to say that we only
discovered the YEARFRAC problems last year. It "should" work.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Phillippe!

Re extending the DATEDIF approach:

Here's the classic:

=DATEDIF(A1,B1,"y") & " y " & DATEDIF(A1,B1,"ym") & " m " &
DATEDIF(A1,B1,"md") & " d"



The trouble comes with Start_Date (eg) 31-Jan-2002 and End_Date (eg)
1-Mar-2004. This returns:

2 y 1 m -1 d



The problem (which you spotted) is that the months have a non-constant
number of days.



My approach would be to stick with constants.



Years and days:

=DATEDIF(A1,B1,"y") & " y " &
B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"

(Note that DATEDIF with yd argument produces errors)

Years, weeks and days:
=DATEDIF(A1,B1,"y") & " y " &
INT((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/7) & " w
" & MOD((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))),7) &
" d"


Years and fractions of a year:
=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY
(A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEA
R(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))


Weeks and Days:
=IF(A1<B1,IF(INT(DATEDIF(A1,B1,"d")/7)=0,MOD(DATEDIF(A1,B1,"d"),7),INT
(DATEDIF(A1,B1,"d")/7) & " wk " & MOD(DATEDIF(A1,B1,"d"),7))&" d","-"&
IF(INT(DATEDIF(B1,A1,"d")/7)=0,MOD(DATEDIF(B1,A1,"d"),7),INT(DATEDIF(B
1,A1,"d")/7) & " wk " & MOD(DATEDIF(B1,A1,"d"),7))&" d")

This one doesn’t report “wk” if the number of days is <=6. Also it
allows the start date to be later than the end-date and in those cases
precedes the entry with a “-“


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top