| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vacation days from date of hire
I need to set up a worksheet that will allow me to calculate vacation days
from Hire date as follows: Employee works for 90 days (intro period), then will accrue 1 vacation day at the beginning of the first of the following month and one additional vacation day for each month after for a maximum of 10 days per year. Thereafter they receive 10 days on January 1st after their first 12 months of service. 4th thru 9th they will recieve 15 days on January 1st. -- cao |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vacation days from date of hire
On Wed, 4 Feb 2009 13:06:22 -0800, srctr
wrote: I need to set up a worksheet that will allow me to calculate vacation days from Hire date as follows: Employee works for 90 days (intro period), then will accrue 1 vacation day at the beginning of the first of the following month and one additional vacation day for each month after for a maximum of 10 days per year. Thereafter they receive 10 days on January 1st after their first 12 months of service. 4th thru 9th they will recieve 15 days on January 1st. Does this mean that they receive 10 days on Jan 1 for years 1,2 and 3; and then 15 days on Jan 1 after that? What happens during the 10th year? Hopefully there is a simpler formula, but this seems to work for the parameters you've mentioned. The two relevant dates are the "HireDate" and the "AsOfDate" The latter is the date being evaluated for how much vacation has been earned. =IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--( DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e), DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR( DATE(YEAR(HireDate),MONTH(HireDate),DAY( HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate -DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate), DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN( AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate), MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5) --ron |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vacation days from date of hire
Yes they get 10 days for years 1, 2, 3; 15 days for years 4 thru 9 and after
10 years they get 20 days. I figured if I had the main formula it wouldn't be to difficult to add the 10+ get 20 days of vacation, but seeing the formula it might be. I am assuming the As of Date could be now or today or a set date like the first of the year? Thanks, I will give this a try. -- cao "Ron Rosenfeld" wrote: On Wed, 4 Feb 2009 13:06:22 -0800, srctr wrote: I need to set up a worksheet that will allow me to calculate vacation days from Hire date as follows: Employee works for 90 days (intro period), then will accrue 1 vacation day at the beginning of the first of the following month and one additional vacation day for each month after for a maximum of 10 days per year. Thereafter they receive 10 days on January 1st after their first 12 months of service. 4th thru 9th they will recieve 15 days on January 1st. Does this mean that they receive 10 days on Jan 1 for years 1,2 and 3; and then 15 days on Jan 1 after that? What happens during the 10th year? Hopefully there is a simpler formula, but this seems to work for the parameters you've mentioned. The two relevant dates are the "HireDate" and the "AsOfDate" The latter is the date being evaluated for how much vacation has been earned. =IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--( DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e), DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR( DATE(YEAR(HireDate),MONTH(HireDate),DAY( HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate -DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate), DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN( AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate), MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5) --ron |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vacation days from date of hire
I am having trouble with the formula. When I paste it into the cell I get an
error "The formula you typed as an error" message on the (Year which is directly following AsOfDate+1-DAY(AsOfDate),DATE(YEAR( ?? -- cao "srctr" wrote: Yes they get 10 days for years 1, 2, 3; 15 days for years 4 thru 9 and after 10 years they get 20 days. I figured if I had the main formula it wouldn't be to difficult to add the 10+ get 20 days of vacation, but seeing the formula it might be. I am assuming the As of Date could be now or today or a set date like the first of the year? Thanks, I will give this a try. -- cao "Ron Rosenfeld" wrote: On Wed, 4 Feb 2009 13:06:22 -0800, srctr wrote: I need to set up a worksheet that will allow me to calculate vacation days from Hire date as follows: Employee works for 90 days (intro period), then will accrue 1 vacation day at the beginning of the first of the following month and one additional vacation day for each month after for a maximum of 10 days per year. Thereafter they receive 10 days on January 1st after their first 12 months of service. 4th thru 9th they will recieve 15 days on January 1st. Does this mean that they receive 10 days on Jan 1 for years 1,2 and 3; and then 15 days on Jan 1 after that? What happens during the 10th year? Hopefully there is a simpler formula, but this seems to work for the parameters you've mentioned. The two relevant dates are the "HireDate" and the "AsOfDate" The latter is the date being evaluated for how much vacation has been earned. =IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--( DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e), DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR( DATE(YEAR(HireDate),MONTH(HireDate),DAY( HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate -DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate), DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN( AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate), MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5) --ron |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vacation days from date of hire
On Thu, 5 Feb 2009 08:13:00 -0800, srctr
wrote: I am having trouble with the formula. When I paste it into the cell I get an error "The formula you typed as an error" message on the (Year which is directly following AsOfDate+1-DAY(AsOfDate),DATE(YEAR( ?? -- cao Some newsgroup readers -- and Google Groups is notorious for this -- put extra formatting symbols within posts. Usually not a problem but can really screw up long formulas. Try removing the line feeds at the end of each line after you've pasted in the formula. And try pasting the formula into the formula bar rather than into the cell. --ron |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vacation days from date of hire
I was already pasting into the Formula Bar. I had cleared out all line feeds
(I thought). I also tried copying into Word as Unformatted Text. I tried this again but made sure I deleted the line feeds. Still problem. I guess I will have to manually type it in -- cao "Ron Rosenfeld" wrote: On Thu, 5 Feb 2009 08:13:00 -0800, srctr wrote: I am having trouble with the formula. When I paste it into the cell I get an error "The formula you typed as an error" message on the (Year which is directly following AsOfDate+1-DAY(AsOfDate),DATE(YEAR( ?? -- cao Some newsgroup readers -- and Google Groups is notorious for this -- put extra formatting symbols within posts. Usually not a problem but can really screw up long formulas. Try removing the line feeds at the end of each line after you've pasted in the formula. And try pasting the formula into the formula bar rather than into the cell. --ron |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vacation days from date of hire
On Thu, 5 Feb 2009 07:32:32 -0800, srctr
wrote: Yes they get 10 days for years 1, 2, 3; 15 days for years 4 thru 9 and after 10 years they get 20 days. I figured if I had the main formula it wouldn't be to difficult to add the 10+ get 20 days of vacation, but seeing the formula it might be. I am assuming the As of Date could be now or today or a set date like the first of the year? Yes. I'd just put it into a cell and NAME the cell. Here's the modification for the 10+years: =IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--( DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e), DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR( DATE(YEAR(HireDate),MONTH(HireDate),DAY( HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate -DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate), DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN( AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate), MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5)+ MAX(0,(YEAR(AsOfDate)-YEAR(MIN( AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate), MONTH(HireDate),DAY(HireDate)+90)),12,31))))-9)*5 --ron |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vacation days from date of hire
It still doesn't work, It gives me an error "the formula you typed has an
error". Should I have the cell formatted already a certain way? -- cao "Ron Rosenfeld" wrote: On Thu, 5 Feb 2009 07:32:32 -0800, srctr wrote: Yes they get 10 days for years 1, 2, 3; 15 days for years 4 thru 9 and after 10 years they get 20 days. I figured if I had the main formula it wouldn't be to difficult to add the 10+ get 20 days of vacation, but seeing the formula it might be. I am assuming the As of Date could be now or today or a set date like the first of the year? Yes. I'd just put it into a cell and NAME the cell. Here's the modification for the 10+years: =IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--( DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e), DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR( DATE(YEAR(HireDate),MONTH(HireDate),DAY( HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate -DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate), DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN( AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate), MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5)+ MAX(0,(YEAR(AsOfDate)-YEAR(MIN( AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate), MONTH(HireDate),DAY(HireDate)+90)),12,31))))-9)*5 --ron |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vacation days from date of hire
On Tue, 10 Feb 2009 16:39:00 -0800, srctr
wrote: It still doesn't work, It gives me an error "the formula you typed has an error". Should I have the cell formatted already a certain way? -- cao What version of Excel do you have? It should work in 2007. It won't work in earlier versions because of nesting limitations. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| FORMULA, DAYS WORKED TO VACATION DAYS | Excel Worksheet Functions | |||
| Calculate vacation days remaining from date of employment | Excel Worksheet Functions | |||
| vacation formula and 1/2 days | Excel Worksheet Functions | |||
| Figuring Vacation Hrs. Earned using Current Date minus Hire Date | Excel Worksheet Functions | |||
| I'm trying to count sick and vacation days | Excel Worksheet Functions | |||
Hybrid Mode
