Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
-- Janice I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
how would you do it on a calculator?? (i have no idea how to
"calculate tenure".) :) if you can explain how you'd do it on a calculator (like 2 + 2 = 4) and what cells "2" is in, and where you want "4", then i can give you the formula (probably). susan On Dec 24, 1:32*pm, Janice Reid wrote: -- Janice I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
=DATEDIF(A7,TODAY(),"y") if the start date is in cell A7.
-- David Biddulph "Janice Reid" wrote in message ... -- Janice I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
On Dec 24, 10:32*am, Janice Reid wrote:
I need to Create a formula that calculates the employees tenure in years. In cell G7[.] But I'm having trouble I don't understand how to do this. For starters, you need to explain how you would calculate it on paper. There is no single way to define "tenure". Suppose the employees hire date is in G6, and the employee is said to reach tenure 10 years later on his anniversary date. One way to compute that is: =date(10+year(G6),month(G6),day(G6)) Format the cell as Date. Caveat regarding Feb 29. The formula above works if the tenure date should be Mar 1 for years in which there is no Feb 28. If the tenure date should be Feb 28 in that case, one solution might be: =if(and(month(G6)=2,day(G6)=29), eomonth(G6,10*12), date(10+year(G6),month(G6),day(G6)) Note that EOMONTH() is a function in the Analysis ToolPak, a Microsoft add-in. If you prefer to stick with standard functions, my solution would be messy. Post back if you are interested (and if no one offers a better solution). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
-- Janice "Janice Reid" wrote: -- Janice I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. The results are 9.98 I need to know how to get this. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
ok, everybody, the answer is 9.98.
=10-0.02 =(20/2)-0.02 =(50*2)-90.02 =(7*13)-(40*2)-1.02 now, guess which one's the question. ------------------------------------------------------- janice, i think you have to give us a little more to go on that that. see questions in previous posts. susan On Dec 24, 2:54*pm, Janice Reid wrote: -- Janice "Janice Reid" wrote: -- Janice I need to Create a formula that calculates the employees tenure in years.. In cell G7 But I'm having trouble I don't understand how to do this. The results are 9.98 I need to know how to get this. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
I need to Create a formula that calculates the employees tenure in years.
In cell G7 But I'm having trouble I don't understand how to do this. The results are 9.98 I need to know how to get this. The results of what are 9.98... your own calculation or something someone else posted for you? By the way, did you read any of the other messages you received to your first posting? There were requests for you to explain your tenure calculation procedure. You must understand when asking questions on line... we have no idea what you want unless you tell us... in detail. We don't know what information you have, what you need to do with it, how you want your results to look, restriction, if any, on how the data is used, etc., etc. If you don't tell us, we have no way of knowing. The more information you provide, the better chance you have of getting an answer that solves your problem. Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
-- Janice "Susan" wrote: how would you do it on a calculator?? (i have no idea how to "calculate tenure".) :) if you can explain how you'd do it on a calculator (like 2 + 2 = 4) and what cells "2" is in, and where you want "4", then i can give you the formula (probably). susan On Dec 24, 1:32 pm, Janice Reid wrote: -- Janice I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. # In cell G7, create a formula that calculates the employees tenure in years. (Dont worry that the result will look odd; well take care of that in the next step.) * Tenure = (Todays date €“ Hire Date)/365 # Format the formula result as Number, 2 decimal places. # Use the fill handle to copy the formula from cell G7 down to the last employee. Make sure you absolute any cell references that need it! (Hint: The result in cell G7 should be 9.98, the tenure in cell G26 should be 4.46.) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
You could use the undocumented DATEDIF Function.
Assuming startdate in A1 =DATEDIF(A1,TODAY(),"y") & " Years " Startdate in A6 and enddate in A7 and you want years, months, days. =DATEDIF(A6,A7,"y") & "years," & DATEDIF(A6,A7,"ym") & "months,"& DATEDIF(A6,A7,"md") & "days," Gord Dibben MS Excel MVP On Mon, 24 Dec 2007 10:32:01 -0800, Janice Reid wrote: I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
business problems don't usually come with detailed instructions and
hints. therefore, i'm assuming this is homework. you've got the formula right in front of you, you only need to put in the cell references for where the two dates are...... * Tenure = (Today's date - Hire Date)/365 =(thecellthatholdstoday'sdate-thecellthatholdsthehiredate)/365 signed, the grinch On Dec 24, 3:16 pm, Janice Reid wrote: -- Janice "Susan" wrote: how would you do it on a calculator?? (i have no idea how to "calculate tenure".) :) if you can explain how you'd do it on a calculator (like 2 + 2 = 4) and what cells "2" is in, and where you want "4", then i can give you the formula (probably). susan On Dec 24, 1:32 pm, Janice Reid wrote: -- Janice I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. # In cell G7, create a formula that calculates the employee's tenure in years. (Don't worry that the result will look odd; we'll take care of that in the next step.) * Tenure = (Today's date - Hire Date)/365 # Format the formula result as Number, 2 decimal places. # Use the fill handle to copy the formula from cell G7 down to the last employee. Make sure you absolute any cell references that need it! (Hint: The result in cell G7 should be 9.98, the tenure in cell G26 should be 4.46.)- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
On Dec 24, 12:16 pm, Janice Reid wrote:
# In cell G7, create a formula that calculates the employee's tenure in years. [....] Tenure = (Today's date - Hire Date)/365 Okay, if "Hire Date" is in G6, then put the following formula into G7: =(today()-G6) / 365 # Format the formula result as Number, 2 decimal places. Follow those instructions. That is, click on G7, then click on FormatCellsNumber. # Use the fill handle to copy the formula from cell G7 down to the last employee. Make sure you absolute any cell references that need it! That wording might suggest that instead of "Today's Date", the instructions intend you to put __a__ (recent) date into a cell, say A1, then substitute $A$1 for "today()" in the formula above. (In fact, perhaps the instructions A1 to be the formula =today().) To understand why you must use $A$1, I suggest that you first make the mistake of writing A1, copy the formula down the column and see what happens. Use ctrl+Z to undo your mistake. Hint: The result in cell G7 should be 9.98, the tenure in cell G26 should be 4.46. Huh!? That's a non sequitur insofar as it conflicts with the definition of tenure above. Or it conflicts with your (mis?)understanding that G7 is the tenure. I suggest that you post the Hire Date for the example that results in 9.98 in G7 and 4.46 in G26. If 9.98 is the tenure (i.e. length of service) in years, the Hire Date might be 1/2/1998 or thereabouts, if today is 12/24/2007. If the tenure is truly 4.46, the Hire Date might be on or about 7/10/2003. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
-- Janice "Susan" wrote: business problems don't usually come with detailed instructions and hints. therefore, i'm assuming this is homework. you've got the formula right in front of you, you only need to put in the cell references for where the two dates are...... * Tenure = (Today's date - Hire Date)/365 =(thecellthatholdstoday'sdate-thecellthatholdsthehiredate)/365 signed, the grinch On Dec 24, 3:16 pm, Janice Reid wrote: -- Janice "Susan" wrote: how would you do it on a calculator?? (i have no idea how to "calculate tenure".) :) if you can explain how you'd do it on a calculator (like 2 + 2 = 4) and what cells "2" is in, and where you want "4", then i can give you the formula (probably). susan On Dec 24, 1:32 pm, Janice Reid wrote: -- Janice I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. # In cell G7, create a formula that calculates the employee's tenure in years. (Don't worry that the result will look odd; we'll take care of that in the next step.) * Tenure = (Today's date - Hire Date)/365 # Format the formula result as Number, 2 decimal places. # Use the fill handle to copy the formula from cell G7 down to the last employee. Make sure you absolute any cell references that need it! (Hint: The result in cell G7 should be 9.98, the tenure in cell G26 should be 4.46.)- Hide quoted text - - Show quoted text - I ask for help not mouth |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
-- Janice "Gord Dibben" wrote: You could use the undocumented DATEDIF Function. Assuming startdate in A1 =DATEDIF(A1,TODAY(),"y") & " Years " Startdate in A6 and enddate in A7 and you want years, months, days. =DATEDIF(A6,A7,"y") & "years," & DATEDIF(A6,A7,"ym") & "months,"& DATEDIF(A6,A7,"md") & "days," Gord Dibben MS Excel MVP On Mon, 24 Dec 2007 10:32:01 -0800, Janice Reid wrote: I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. Thanks for the help so much |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
well, you're right - i apologize for getting frustrated with your lack
of explanation. but as rick said, also, it's very hard to offer any help when nobody understands what's going on. susan On Dec 24, 6:50*pm, Janice Reid wrote: I ask for help not mouth -- Janice "Susan" wrote: business problems don't usually come with detailed instructions and hints. *therefore, i'm assuming this is homework. you've got the formula right in front of you, you only need to put in the cell references for where the two dates are...... * Tenure = (Today's date - Hire Date)/365 =(thecellthatholdstoday'sdate-thecellthatholdsthehiredate)/365 signed, the grinch On Dec 24, 3:16 pm, Janice Reid wrote: -- Janice "Susan" wrote: how would you do it on a calculator?? *(i have no idea how to "calculate tenure".) :) if you can explain how you'd do it on a calculator (like 2 + 2 = 4) and what cells "2" is in, and where you want "4", then i can give you the formula (probably). susan On Dec 24, 1:32 pm, Janice Reid wrote: -- Janice I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. # In cell G7, create a formula that calculates the employee's tenure in years. (Don't worry that the result will look odd; we'll take care of that in the next step.) * * * Tenure = (Today's date - Hire Date)/365 # Format the formula result as Number, 2 decimal places. # Use the fill handle to copy the formula from cell G7 down to the last employee. Make sure you absolute any cell references that need it! (Hint: The result in cell G7 should be 9.98, the tenure in cell G26 should be 4.46.)- Hide quoted text - - Show quoted text - I ask for help not mouth- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tenure
OMG, I have been reading through a lot of the question and answers (great
forum) and this one shocked me. Susan, she was not professional at all. But I loved your reply. Keep helping us dough heads! Thanks "Susan" wrote: well, you're right - i apologize for getting frustrated with your lack of explanation. but as rick said, also, it's very hard to offer any help when nobody understands what's going on. susan On Dec 24, 6:50 pm, Janice Reid wrote: I ask for help not mouth -- Janice "Susan" wrote: business problems don't usually come with detailed instructions and hints. therefore, i'm assuming this is homework. you've got the formula right in front of you, you only need to put in the cell references for where the two dates are...... * Tenure = (Today's date - Hire Date)/365 =(thecellthatholdstoday'sdate-thecellthatholdsthehiredate)/365 signed, the grinch On Dec 24, 3:16 pm, Janice Reid wrote: -- Janice "Susan" wrote: how would you do it on a calculator?? (i have no idea how to "calculate tenure".) :) if you can explain how you'd do it on a calculator (like 2 + 2 = 4) and what cells "2" is in, and where you want "4", then i can give you the formula (probably). susan On Dec 24, 1:32 pm, Janice Reid wrote: -- Janice I need to Create a formula that calculates the employees tenure in years. In cell G7 But I'm having trouble I don't understand how to do this. # In cell G7, create a formula that calculates the employee's tenure in years. (Don't worry that the result will look odd; we'll take care of that in the next step.) * Tenure = (Today's date - Hire Date)/365 # Format the formula result as Number, 2 decimal places. # Use the fill handle to copy the formula from cell G7 down to the last employee. Make sure you absolute any cell references that need it! (Hint: The result in cell G7 should be 9.98, the tenure in cell G26 should be 4.46.)- Hide quoted text - - Show quoted text - I ask for help not mouth- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
working with employee tenure and grouping | Excel Worksheet Functions | |||
automated employee tenure summary using Customized Charts with VBA | Charts and Charting in Excel |