Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eligible Vacation Days Calculator
Need to calculate if someone has worked X months since DOH, they are entitled
to X vacation days: over 6 months = 1 week vacation; over 1 year = 2 weeks; over 5 years = 3 weeks; over 10 years = 4 weeks; over 15 years = 5 weeks. D2 E2 F2 Date of Hire Today's Date =DATEDIF($D2,$E2,"m") Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eligible Vacation Days Calculator
=(DATEDIF(DOH,TODAY(),"M")=6)+(DATEDIF(DOH,TODAY( ),"Y")=1)+(DATEDIF(DOH,TODAY(),"Y")=5)+(DATEDIF( DOH,TODAY(),"Y")=10)+(DATEDIF(DOH,TODAY(),"Y")=1 5)
-- __________________________________ HTH Bob "tfarley" wrote in message ... Need to calculate if someone has worked X months since DOH, they are entitled to X vacation days: over 6 months = 1 week vacation; over 1 year = 2 weeks; over 5 years = 3 weeks; over 10 years = 4 weeks; over 15 years = 5 weeks. D2 E2 F2 Date of Hire Today's Date =DATEDIF($D2,$E2,"m") Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eligible Vacation Days Calculator
You rock! Thank you!!!
"Bob Phillips" wrote: =(DATEDIF(DOH,TODAY(),"M")=6)+(DATEDIF(DOH,TODAY( ),"Y")=1)+(DATEDIF(DOH,TODAY(),"Y")=5)+(DATEDIF( DOH,TODAY(),"Y")=10)+(DATEDIF(DOH,TODAY(),"Y")=1 5) -- __________________________________ HTH Bob "tfarley" wrote in message ... Need to calculate if someone has worked X months since DOH, they are entitled to X vacation days: over 6 months = 1 week vacation; over 1 year = 2 weeks; over 5 years = 3 weeks; over 10 years = 4 weeks; over 15 years = 5 weeks. D2 E2 F2 Date of Hire Today's Date =DATEDIF($D2,$E2,"m") Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eligible Vacation Days Calculator
In another location (I suggest a new sheet in the same workbook) enter the
following: Col A Col B 6 1 Week 12 2 Weeks 60 3 Weeks 120 4 Weeks 180 5 Weeks Select the 10 cells above and in the range name box type 'lookup'. Now back in F2 use the following formula: =VLOOKUP(DATEDIF($d2,$e2,"m"),lookup,2) You can now customize the month or return values in the lookup table to suit your needs without changing any formulas. "Bob Phillips" wrote: =(DATEDIF(DOH,TODAY(),"M")=6)+(DATEDIF(DOH,TODAY( ),"Y")=1)+(DATEDIF(DOH,TODAY(),"Y")=5)+(DATEDIF( DOH,TODAY(),"Y")=10)+(DATEDIF(DOH,TODAY(),"Y")=1 5) -- __________________________________ HTH Bob "tfarley" wrote in message ... Need to calculate if someone has worked X months since DOH, they are entitled to X vacation days: over 6 months = 1 week vacation; over 1 year = 2 weeks; over 5 years = 3 weeks; over 10 years = 4 weeks; over 15 years = 5 weeks. D2 E2 F2 Date of Hire Today's Date =DATEDIF($D2,$E2,"m") Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eligible Vacation Days Calculator
In F2 =DATEDIF($D2,$E2,"m")/12
In G2 =LOOKUP(F2,{0,0.5,1,5,10,15},{0,1,2,3,4,5}) Gord Dibben MS Excel MVP On Tue, 24 Jun 2008 12:29:01 -0700, tfarley wrote: Need to calculate if someone has worked X months since DOH, they are entitled to X vacation days: over 6 months = 1 week vacation; over 1 year = 2 weeks; over 5 years = 3 weeks; over 10 years = 4 weeks; over 15 years = 5 weeks. D2 E2 F2 Date of Hire Today's Date =DATEDIF($D2,$E2,"m") Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vacation formula and 1/2 days | Excel Worksheet Functions | |||
How do I add "accrued vacation days" column to the Employee Attend | Excel Worksheet Functions | |||
I'm trying to count sick and vacation days | Excel Worksheet Functions | |||
Keeping track of Sickdays, Vacation days, and hours for each. | Excel Worksheet Functions | |||
How do I track vacation days on Excel? | Setting up and Configuration of Excel |