calculating accumulated vacation time

[rpiescik]

Hello, I'm looking for a little help here. I'm trying to figure out a
excel formula to do the following:

From the date of hire our employees accrue vacation time at .42 day
per month for the first 2 years. After the first two years it steps u
to .83 days per month for years 3 and 4. Then in year 5 it goes to 1.2
days per month.
Currently my excel spreadsheet has column A with the employees name
column B has the employees hire date. I would like the worksheet t
calculate every time it is open so that I have up to the minut
results. Thanks

 

[Daniel.M]

Hi,

So you're counting by months.
Use the differences between the rate (.42 then .41 more then .42 more)

=SUMPRODUCT({0.42,0.41,0.42}*(DATEDIF(hiredate,TODAY(),"m")-
{0,24,48})*(DATEDIF(hiredate,TODAY(),"m")>{0,24,48}))

Regards,

Daniel M.

 

[rpiescik]

I have tried the formula that was given. Thank you.

I get the result of #NAME? in the cell field.

for "hiredate" that info comes from column 'b' of my worksheet.
for "today()" that info comes from Cell Range A1 of the worksheet.
What is "m" to represent? Is this to represent the total number o
months?

On my worksheet in column 'c' I have the following giving me the tota
number of months as a whole number. The formula that I have there i
this =DATEDIF(B3,NOW(),"m").

So what am I missing here? Thanks for the help. I look forward to th
reply

 

[Frank Kabel]

Hi
post the exact formula you have used

 

[Daniel.M]

Hi,

You're missing to substitute the names in my formulas with the proper cell
references.

If you have a date in B3 (corresponding to a hiredate) and today's date in A1:

=SUMPRODUCT({0.42,0.41,0.42}*(DATEDIF(B3,A1,"m")-
{0,24,48})*(DATEDIF(B3,A1,"m")>{0,24,48}))

Regards,

Daniel M.

 

[rpiescik]

Thank you, it worked great.

Now how do I handle the exceptions.

For example, I have an employee who has different vacation terms. The
are allowed 2 weeks for each of the first two years and then startin
in the third year they are allowed 3 weeks. How would that work wit
the formula given. Look forward to the reply. Thanks