DateDif Average? Damn DateDif

I'm not sure this is even possible.

I am calculating the length of time someone has worked for the company:
Column; Row A1,
Hire Date MM/DD/YYYY

Column; Row B1,
=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days"

Which gives me a result like: "4 years, 5 months, 10 days"

I want to be able to average the results from column B, thereby producing the average "years, months, and days" worked. Just not sure how to get there. Please help I'll loose my job... Ah, maybe not, but you know.

Thanks everyone...

First, I think I'd use Today() instead of Now() (it just makes more sense to me
and I'm kind of ****-retentive).

But couldn't you just take the average start date and do the datedif() stuff:

=datedif(average(a1:A100),today(),"y") & "years, "......



UTCHELP wrote:
>
> I'm not sure this is even possible.
>
> I am calculating the length of time someone has worked for the
> company:
> Column; Row A1,
> Hire Date MM/DD/YYYY
>
> Column; Row B1,
> =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
> months, " & DATEDIF(A1,NOW(),"md") & " days"
>
> Which gives me a result like: "4 years, 5 months, 10 days"
>
> I want to be able to average the results from column B, thereby
> producing the average "years, months, and days" worked. Just not sure
> how to get there. Please help I'll loose my job... Ah, maybe not, but
> you know.
>
> Thanks everyone...
>
> --
> UTCHELP
> ------------------------------------------------------------------------
> UTCHELP's Profile: http://www.excelforum.com/member.php...o&userid=27894
> View this thread: http://www.excelforum.com/showthread...hreadid=473980

--

Dave Peterson

Use the days only to average and then convert to years etc

--
Regards,

Peo Sjoblom

(No private emails please)


"UTCHELP" <[email protected]> wrote in
message news:[email protected]...
>
> I'm not sure this is even possible.
>
> I am calculating the length of time someone has worked for the
> company:
> Column; Row A1,
> Hire Date MM/DD/YYYY
>
> Column; Row B1,
> =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
> months, " & DATEDIF(A1,NOW(),"md") & " days"
>
> Which gives me a result like: "4 years, 5 months, 10 days"
>
> I want to be able to average the results from column B, thereby
> producing the average "years, months, and days" worked. Just not sure
> how to get there. Please help I'll loose my job... Ah, maybe not, but
> you know.
>
> Thanks everyone...
>
>
> --
> UTCHELP
> ------------------------------------------------------------------------
> UTCHELP's Profile:
> http://www.excelforum.com/member.php...o&userid=27894
> View this thread: http://www.excelforum.com/showthread...hreadid=473980
>

BRILLIANT, Dave!!!

On Thu, 06 Oct 2005 20:14:32 -0500, Dave Peterson <[email protected]>
wrote:

>First, I think I'd use Today() instead of Now() (it just makes more sense to
me
>and I'm kind of ****-retentive).
>
>But couldn't you just take the average start date and do the datedif() stuff:
>
>=datedif(average(a1:A100),today(),"y") & "years, "......
>
>
>
>UTCHELP wrote:
>>
>> I'm not sure this is even possible.
>>
>> I am calculating the length of time someone has worked for the
>> company:
>> Column; Row A1,
>> Hire Date MM/DD/YYYY
>>
>> Column; Row B1,
>> =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
>> months, " & DATEDIF(A1,NOW(),"md") & " days"
>>
>> Which gives me a result like: "4 years, 5 months, 10 days"
>>
>> I want to be able to average the results from column B, thereby
>> producing the average "years, months, and days" worked. Just not sure
>> how to get there. Please help I'll loose my job... Ah, maybe not, but
>> you know.
>>
>> Thanks everyone...
>>
>> --
>> UTCHELP
>> ------------------------------------------------------------------------
>> UTCHELP's Profile:
http://www.excelforum.com/member.php...o&userid=27894
>> View this thread: http://www.excelforum.com/showthread...hreadid=473980

The ****-retentive portion or the formula??? <vbg>

(Could be either!)

Myrna Larson wrote:
>
> BRILLIANT, Dave!!!
>
> On Thu, 06 Oct 2005 20:14:32 -0500, Dave Peterson <[email protected]>
> wrote:
>
> >First, I think I'd use Today() instead of Now() (it just makes more sense to
> me
> >and I'm kind of ****-retentive).
> >
> >But couldn't you just take the average start date and do the datedif() stuff:
> >
> >=datedif(average(a1:A100),today(),"y") & "years, "......
> >
> >
> >
> >UTCHELP wrote:
> >>
> >> I'm not sure this is even possible.
> >>
> >> I am calculating the length of time someone has worked for the
> >> company:
> >> Column; Row A1,
> >> Hire Date MM/DD/YYYY
> >>
> >> Column; Row B1,
> >> =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
> >> months, " & DATEDIF(A1,NOW(),"md") & " days"
> >>
> >> Which gives me a result like: "4 years, 5 months, 10 days"
> >>
> >> I want to be able to average the results from column B, thereby
> >> producing the average "years, months, and days" worked. Just not sure
> >> how to get there. Please help I'll loose my job... Ah, maybe not, but
> >> you know.
> >>
> >> Thanks everyone...
> >>
> >> --
> >> UTCHELP
> >> ------------------------------------------------------------------------
> >> UTCHELP's Profile:
> http://www.excelforum.com/member.php...o&userid=27894
> >> View this thread: http://www.excelforum.com/showthread...hreadid=473980

--

Dave Peterson

Hey thanks for the great advise Dave. No need to convert between days and years just a reordering of the code and it worked great thanks. And I did change the NOW() to a Today(), just to honor your **** rentitiveness.

I, for one, will sleep better tonight!

UTCHELP wrote:
>
> Hey thanks for the great advise Dave. No need to convert between days
> and years just a reordering of the code and it worked great thanks. And
> I did change the NOW() to a Today(), just to honor your ****
> rentitiveness.
>
> --
> UTCHELP
> ------------------------------------------------------------------------
> UTCHELP's Profile: http://www.excelforum.com/member.php...o&userid=27894
> View this thread: http://www.excelforum.com/showthread...hreadid=473980

--

Dave Peterson

I'm trying to get an average "length of service" for staff. I've tried your
suggestion but can't get it to work. The formula I'm using is:

=DATEDIF(D5,TODAY(),"y") & "." & DATEDIF(D5,TODAY(),"ym")

Any additional tips please?
Many thanks
Yvonne

"Dave Peterson" wrote:

> First, I think I'd use Today() instead of Now() (it just makes more sense to me
> and I'm kind of ****-retentive).
>
> But couldn't you just take the average start date and do the datedif() stuff:
>
> =datedif(average(a1:A100),today(),"y") & "years, "......
>
>
>
> UTCHELP wrote:
> >
> > I'm not sure this is even possible.
> >
> > I am calculating the length of time someone has worked for the
> > company:
> > Column; Row A1,
> > Hire Date MM/DD/YYYY
> >
> > Column; Row B1,
> > =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
> > months, " & DATEDIF(A1,NOW(),"md") & " days"
> >
> > Which gives me a result like: "4 years, 5 months, 10 days"
> >
> > I want to be able to average the results from column B, thereby
> > producing the average "years, months, and days" worked. Just not sure
> > how to get there. Please help I'll loose my job... Ah, maybe not, but
> > you know.
> >
> > Thanks everyone...
> >
> > --
> > UTCHELP
> > ------------------------------------------------------------------------
> > UTCHELP's Profile: http://www.excelforum.com/member.php...o&userid=27894
> > View this thread: http://www.excelforum.com/showthread...hreadid=473980
>
> --
>
> Dave Peterson
>

Dave's suggestion would result in a formula of

=DATEDIF(AVERAGE(A1:A100),TODAY(),"y") & " years, " &
DATEDIF(AVERAGE(A1:A100),TODAY(),"ym") & "months, " &
DATEDIF(AVERAGE(A1:A100),TODAY(),"md") & " days"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"yvonneb" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to get an average "length of service" for staff. I've tried
your
> suggestion but can't get it to work. The formula I'm using is:
>
> =DATEDIF(D5,TODAY(),"y") & "." & DATEDIF(D5,TODAY(),"ym")
>
> Any additional tips please?
> Many thanks
> Yvonne
>
> "Dave Peterson" wrote:
>
> > First, I think I'd use Today() instead of Now() (it just makes more
sense to me
> > and I'm kind of ****-retentive).
> >
> > But couldn't you just take the average start date and do the datedif()
stuff:
> >
> > =datedif(average(a1:A100),today(),"y") & "years, "......
> >
> >
> >
> > UTCHELP wrote:
> > >
> > > I'm not sure this is even possible.
> > >
> > > I am calculating the length of time someone has worked for the
> > > company:
> > > Column; Row A1,
> > > Hire Date MM/DD/YYYY
> > >
> > > Column; Row B1,
> > > =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
> > > months, " & DATEDIF(A1,NOW(),"md") & " days"
> > >
> > > Which gives me a result like: "4 years, 5 months, 10 days"
> > >
> > > I want to be able to average the results from column B, thereby
> > > producing the average "years, months, and days" worked. Just not sure
> > > how to get there. Please help I'll loose my job... Ah, maybe not, but
> > > you know.
> > >
> > > Thanks everyone...
> > >
> > > --
> > > UTCHELP
> >
> ------------------------------------------------------------------------
> > > UTCHELP's Profile:
http://www.excelforum.com/member.php...o&userid=27894
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=473980
> >
> > --
> >
> > Dave Peterson
> >

OK, I've put in the following forumula and asked it to look at the actual
date column (not the length of service result). However it keeps coming up
as #VALUE!

=DATEDIF(AVERAGE(J4:J62),TODAY(),"y") & "." & DATEDIF(J4:J62,TODAY(),"ym")

Am i being really stupid here?
Thanks
Yvonne



"yvonneb" wrote:

> I'm trying to get an average "length of service" for staff. I've tried your
> suggestion but can't get it to work. The formula I'm using is:
>
> =DATEDIF(D5,TODAY(),"y") & "." & DATEDIF(D5,TODAY(),"ym")
>
> Any additional tips please?
> Many thanks
> Yvonne
>
> "Dave Peterson" wrote:
>
> > First, I think I'd use Today() instead of Now() (it just makes more sense to me
> > and I'm kind of ****-retentive).
> >
> > But couldn't you just take the average start date and do the datedif() stuff:
> >
> > =datedif(average(a1:A100),today(),"y") & "years, "......
> >
> >
> >
> > UTCHELP wrote:
> > >
> > > I'm not sure this is even possible.
> > >
> > > I am calculating the length of time someone has worked for the
> > > company:
> > > Column; Row A1,
> > > Hire Date MM/DD/YYYY
> > >
> > > Column; Row B1,
> > > =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
> > > months, " & DATEDIF(A1,NOW(),"md") & " days"
> > >
> > > Which gives me a result like: "4 years, 5 months, 10 days"
> > >
> > > I want to be able to average the results from column B, thereby
> > > producing the average "years, months, and days" worked. Just not sure
> > > how to get there. Please help I'll loose my job... Ah, maybe not, but
> > > you know.
> > >
> > > Thanks everyone...
> > >
> > > --
> > > UTCHELP
> > > ------------------------------------------------------------------------
> > > UTCHELP's Profile: http://www.excelforum.com/member.php...o&userid=27894
> > > View this thread: http://www.excelforum.com/showthread...hreadid=473980
> >
> > --
> >
> > Dave Peterson
> >

You need to confirm that formula with control+shift+enter, not just with
enter.

YvonneB wrote:
> OK, I've put in the following forumula and asked it to look at the actual
> date column (not the length of service result). However it keeps coming up
> as #VALUE!
>
> =DATEDIF(AVERAGE(J4:J62),TODAY(),"y") & "." & DATEDIF(J4:J62,TODAY(),"ym")
>
> Am i being really stupid here?
> Thanks
> Yvonne
[...]

You sure?

It seemed to work ok with just enter for me.

Aladin Akyurek wrote:
>
> You need to confirm that formula with control+shift+enter, not just with
> enter.
>
> YvonneB wrote:
> > OK, I've put in the following forumula and asked it to look at the actual
> > date column (not the length of service result). However it keeps coming up
> > as #VALUE!
> >
> > =DATEDIF(AVERAGE(J4:J62),TODAY(),"y") & "." & DATEDIF(J4:J62,TODAY(),"ym")
> >
> > Am i being really stupid here?
> > Thanks
> > Yvonne
> [...]

--

Dave Peterson

Do you have any errors in J4:J62?

Don't forget to look at any hidden rows (hidden manually or from data|Filter)/

YvonneB wrote:
>
> OK, I've put in the following forumula and asked it to look at the actual
> date column (not the length of service result). However it keeps coming up
> as #VALUE!
>
> =DATEDIF(AVERAGE(J4:J62),TODAY(),"y") & "." & DATEDIF(J4:J62,TODAY(),"ym")
>
> Am i being really stupid here?
> Thanks
> Yvonne
>
> "yvonneb" wrote:
>
> > I'm trying to get an average "length of service" for staff. I've tried your
> > suggestion but can't get it to work. The formula I'm using is:
> >
> > =DATEDIF(D5,TODAY(),"y") & "." & DATEDIF(D5,TODAY(),"ym")
> >
> > Any additional tips please?
> > Many thanks
> > Yvonne
> >
> > "Dave Peterson" wrote:
> >
> > > First, I think I'd use Today() instead of Now() (it just makes more sense to me
> > > and I'm kind of ****-retentive).
> > >
> > > But couldn't you just take the average start date and do the datedif() stuff:
> > >
> > > =datedif(average(a1:A100),today(),"y") & "years, "......
> > >
> > >
> > >
> > > UTCHELP wrote:
> > > >
> > > > I'm not sure this is even possible.
> > > >
> > > > I am calculating the length of time someone has worked for the
> > > > company:
> > > > Column; Row A1,
> > > > Hire Date MM/DD/YYYY
> > > >
> > > > Column; Row B1,
> > > > =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
> > > > months, " & DATEDIF(A1,NOW(),"md") & " days"
> > > >
> > > > Which gives me a result like: "4 years, 5 months, 10 days"
> > > >
> > > > I want to be able to average the results from column B, thereby
> > > > producing the average "years, months, and days" worked. Just not sure
> > > > how to get there. Please help I'll loose my job... Ah, maybe not, but
> > > > you know.
> > > >
> > > > Thanks everyone...
> > > >
> > > > --
> > > > UTCHELP
> > > > ------------------------------------------------------------------------
> > > > UTCHELP's Profile: http://www.excelforum.com/member.php...o&userid=27894
> > > > View this thread: http://www.excelforum.com/showthread...hreadid=473980
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson

Hi Yvonne

Aladin has posted a solution telling you to make it an array formula.
Just making your formula an Array formula, stops the #VALUE being returned.
However, it just returns the Datedif value for cell J4, not the average of
the range.

The formula (again array entered with Ctrl+Shift+Enter)
=AVERAGE(--(DATEDIF(J4:J15,TODAY(),"y")&"."&DATEDIF(J4:J15,TODAY(),"ym")))
will give the average for the range J4:J15.

This returns the Average Year + Average Month, not the decimal average of
the year and month. I just used an array from J4:J15 and entered dates of
08/01/44 (8th January 19944) in J4 through to 08/12/44 in J15
The above formula gave me the answer of 61.22583

However, using true decimal year by taking the formula (array entered)
=AVERAGE(DATEDIF(J4:J15,TODAY(),"y")+DATEDIF(J4:J15,TODAY(),"ym")/12)
returned the answer (correctly, I think) as 61.29167 or 61 years 3.5 months.

The difference is around 1.25 months, but this is in a population with an
equal distribution of ages between 60 years 10 months and 61 years 9 months.
If the distribution were not even, then the "error" could be larger, which
may or may not be material in respect of what you wish to do with the result.

Regards

Roger Govier

YvonneB wrote:
> OK, I've put in the following forumula and asked it to look at the actual
> date column (not the length of service result). However it keeps coming up
> as #VALUE!
>
> =DATEDIF(AVERAGE(J4:J62),TODAY(),"y") & "." & DATEDIF(J4:J62,TODAY(),"ym")
>
> Am i being really stupid here?
> Thanks
> Yvonne
>
>
>
> "yvonneb" wrote:
>
>
>>I'm trying to get an average "length of service" for staff. I've tried your
>>suggestion but can't get it to work. The formula I'm using is:
>>
>>=DATEDIF(D5,TODAY(),"y") & "." & DATEDIF(D5,TODAY(),"ym")
>>
>>Any additional tips please?
>>Many thanks
>>Yvonne
>>
>>"Dave Peterson" wrote:
>>
>>
>>>First, I think I'd use Today() instead of Now() (it just makes more sense to me
>>>and I'm kind of ****-retentive).
>>>
>>>But couldn't you just take the average start date and do the datedif() stuff:
>>>
>>>=datedif(average(a1:A100),today(),"y") & "years, "......
>>>
>>>
>>>
>>>UTCHELP wrote:
>>>
>>>>I'm not sure this is even possible.
>>>>
>>>>I am calculating the length of time someone has worked for the
>>>>company:
>>>>Column; Row A1,
>>>>Hire Date MM/DD/YYYY
>>>>
>>>>Column; Row B1,
>>>>=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
>>>>months, " & DATEDIF(A1,NOW(),"md") & " days"
>>>>
>>>>Which gives me a result like: "4 years, 5 months, 10 days"
>>>>
>>>>I want to be able to average the results from column B, thereby
>>>>producing the average "years, months, and days" worked. Just not sure
>>>>how to get there. Please help I'll loose my job... Ah, maybe not, but
>>>>you know.
>>>>
>>>>Thanks everyone...
>>>>
>>>>--
>>>>UTCHELP
>>>>------------------------------------------------------------------------
>>>>UTCHELP's Profile: http://www.excelforum.com/member.php...o&userid=27894
>>>>View this thread: http://www.excelforum.com/showthread...hreadid=473980
>>>
>>>--
>>>
>>>Dave Peterson
>>>

The info you've given so far is excellent - thanks, and I have been using the
"NOW" instead of TODAY.

Now i have a new twist on the same theme. I want to calculate the average
length of service of ex-employees. I'm using the following:

J8 = start date (2-Nov-04)
K8 = leaving date (6-Sep-05)

=DATEDIF(J8,K8,"y") & "." & DATEDIF(J8,K8,"ym")

which gives me a result of 0.10 (0 yrs/10 mths)

I have a column of results in yrs/mths which I want to average out. Can
this be done?
Many thanks
Yvonne



"Dave Peterson" wrote:

> First, I think I'd use Today() instead of Now() (it just makes more sense to me
> and I'm kind of ****-retentive).
>
> But couldn't you just take the average start date and do the datedif() stuff:
>
> =datedif(average(a1:A100),today(),"y") & "years, "......
>
>
>
> UTCHELP wrote:
> >
> > I'm not sure this is even possible.
> >
> > I am calculating the length of time someone has worked for the
> > company:
> > Column; Row A1,
> > Hire Date MM/DD/YYYY
> >
> > Column; Row B1,
> > =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
> > months, " & DATEDIF(A1,NOW(),"md") & " days"
> >
> > Which gives me a result like: "4 years, 5 months, 10 days"
> >
> > I want to be able to average the results from column B, thereby
> > producing the average "years, months, and days" worked. Just not sure
> > how to get there. Please help I'll loose my job... Ah, maybe not, but
> > you know.
> >
> > Thanks everyone...
> >
> > --
> > UTCHELP
> > ------------------------------------------------------------------------
> > UTCHELP's Profile: http://www.excelforum.com/member.php...o&userid=27894
> > View this thread: http://www.excelforum.com/showthread...hreadid=473980
>
> --
>
> Dave Peterson
>

the average suggested will not work properly as it is averaging the years separately from the months and from the days and providing thre averages which is not correct. the answer is to do the following:
1. get today() - start date for each employee (this will give the number of days between the start and today)
2. sum up the total number of days from 1. above and then divide this by the number of employees. Ensure you use the rounddown() function.
3. in a separate cell type today() - result from 2. (this will give you the average starting day across all employees)
4. do a datediff between today() and 3.