excel

[Rae]

How do I create an accural spread sheet for vacations.

this is the info that I have
1st yr - 1 week @ .77 per pay period
2nd - 10th yr - 2 weeks @ 1.54 per pay period
10th + 3 weeks @ 2.31 per pay period

 

[Sandy Mann]

Rae,

Your question is not very clear, (at least to me).

1st yr - 1 week @ .77 per pay period

Does that mean from the hire date to the 1st year anniversary the employee
gets 1 weeks vacation?

What does @ .77 per pay period mean?

What is a pay period?

Try restating your needs rmembering that we do not know how your company
works.
--
Regards,

Sandy

In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Rae]

from the date of hire the employee starts to accural vacation time. After one
year of service they are entitled to 1 week.
1st year would be 52*.77=40
2nd - 10th would be 52*1.54 = 80
10th + would be 2.31*52 =120

What I want to do is create a spread sheet where I can enter the date of
hire and it will calculate the vacation time.

 

[Sandy Mann]

To return the number of hours due for the number of years of service try:

=IF(DATEDIF(F11,TODAY(),"y")<1,40,IF(DATEDIF(F11,TODAY(),"y")<10,80,120))

with the start date in F11

If you want something different then post back.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

If you want to work out how many hours are due based on a weeks in the
currect vacation year then try:

=INT(IF(DATEDIF(F11,TODAY(),"y")<1,40/52,IF(DATEDIF(F11,TODAY(),"y")<10,80/52,120/52))*CEILING((TODAY()-DATE(YEAR(F11)+DATEDIF(F11,TODAY(),"y"),MONTH(F11),DAY(F11)))/7,1))

format the cell as General
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Slightly shorter and with fewer function calls and it also allows for F1
being empty:

=IF(F11="","",INT(LOOKUP(DATEDIF(F11,TODAY(),"Y"),{0;1;10},{40,80,120})/52*CEILING((TODAY()-DATE(YEAR(F11)+DATEDIF(F11,TODAY(),"y"),MONTH(F11),DAY(F11)))/7,1)))
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk