| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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... -- UTCHELP ------------------------------------------------------------------------ UTCHELP's Profile: http://www.excelforum.com/member.php...o&userid=27894 View this thread: http://www.excelforum.com/showthread...hreadid=473980 |
|
#2
|
|||
|
|||
|
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, "...... 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 |
|
#3
|
|||
|
|||
|
BRILLIANT, Dave!!!
On Thu, 06 Oct 2005 20:14:32 -0500, 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 anal-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 |
|
#4
|
|||
|
|||
|
The anal-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 wrote: 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, "...... 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 |
|
#5
|
|||
|
|||
|
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. -- UTCHELP ------------------------------------------------------------------------ UTCHELP's Profile: http://www.excelforum.com/member.php...o&userid=27894 View this thread: http://www.excelforum.com/showthread...hreadid=473980 |
|
#6
|
|||
|
|||
|
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 anal 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 |
|
#7
|
|||
|
|||
|
DateDif Average? Damn DateDif
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 anal-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 |
|
#8
|
|||
|
|||
|
DateDif Average? Damn DateDif
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" wrote in message ... 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 anal-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 |
|
#9
|
|||
|
|||
|
DateDif Average? Damn DateDif
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 anal-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 |
|
#10
|
|||
|
|||
|
DateDif Average? Damn DateDif
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 [...] |
|
#11
|
|||
|
|||
|
DateDif Average? Damn DateDif
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 |
|
#12
|
|||
|
|||
|
DateDif Average? Damn DateDif
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 anal-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 |
|
#13
|
|||
|
|||
|
DateDif Average? Damn DateDif
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,TO DAY(),"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:J1 5,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 anal-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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DateDif Average? Damn DateDif
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 anal-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 |
|
#15
|
|||
|
|||
|
Use the days only to average and then convert to years etc
-- Regards, Peo Sjoblom (No private emails please) "UTCHELP" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| plotted Average | Charts and Charting in Excel | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
| What is this kind of average called? | Excel Worksheet Functions | |||
| average function in Excel 2002 | New Users to Excel | |||
Hybrid Mode
