DateDif Average? Damn DateDif

[UTCHELP]

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...

 

[Dave Peterson]

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

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

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

 

[Peo Sjoblom]

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

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Myrna Larson]

BRILLIANT, Dave!!!

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

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

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

 

[Dave Peterson]

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

(Could be either!)

 

[UTCHELP]

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 anal
rentitiveness.

 

[Dave Peterson]

I, for one, will sleep better tonight!

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 anal
rentitiveness.

 

[Guest]

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

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

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

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

 

[Bob Phillips]

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)

 

[Guest]

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

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

 

[Aladin Akyurek]

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

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]

You sure?

It seemed to work ok with just enter for me.

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

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)/

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

 

[Roger Govier]

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

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



:

 

[Guest]

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

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

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

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