vacation hours accrual

[lsantiago]

I am looking for a formula to calculate hours accrued per pay period. We get paid on the 15th and the last day of the month. less than 5 years = 4 hours per pay period, more than 5= 6 hours per pay period.

[Don]

Hi and welcome to the forum!

Without knowing more, it looks like you are just looking for a simple IF statement. Something like this:

Code: Select all

=IF(A1<5,4,6)

where cell A1 would hold the value compared against 5.

[lsantiago]

I have that part, but how do I calculate so that it accrues every pay period on the 15th and 30th?

[Don]

That depends on how you have your spreadsheet setup. Can you attach it or a sample of it here?

[lsantiago]

I have attached the file. Thanks for your help.

[Sisyphus]

Hi,
I'm afraid the information you have provided isn't enough to create a formula, but it suffices to start a train of thought:-

In order to calculate accrual you need to have a starting point. Say, it is January 1 of whatever year your sheet is dealing with. This date should be on the sheet. You start counting from there, crediting 1 Unit every 15 days. Later you convert the units to hours by multiplying either with 4 or with 6. That is easy. The hard part comes earlier.

Are you actually crediting 1 unit every 15 days? Or are you crediting 1/15th unit every day? Are you taking workdays into account? In other words, are you, in fact, crediting - say - 24/222 units every workday (where 24 is the number of half-months in the year and 222 the assumed number of workdays)? Such thoughts come to mind because there is no 30th day in February, and what are you going to show in your sheet on February 28 of a leap year? How does your sheet change from Friday to Monday?

Whichever way you decide this, you will end up by calculating the number of units accrued from the beginning of the year. In the March column you will see the total number of units earned from the beginning of the year minus the credits shown in Jan and Feb. Therefore your formula must be able to write fixed numbers at the end of a month, meaning, when TODAY() has advanced beyond the end of that month the number to be written in the cell should no longer be based on calculation but be fixed at 2. Depending upon how you calculate your accrual this might lead to the balance on March 1 to be negative. But you should definitely have the month's date in each column. You can hide it behind the months' names by formatting the cell to show the abbreviated name but have a full date in the cell that you can refer to in your formula.

I am ignoring the 15th for now because it makes life just harder and I wonder what is the point. How often does some one leave on the 15th? Anyway, how are you rounding these numbers when some one leaves on 10th? Say, for the 15th salary statement your sheet wants to grant 3 hours and 50 minutes. Will this be rounded to 4 anyway? I think it is justifiable to ignore the 15th so long as your sheet doesn't have columns for it.

I hope this analysis will prove somewhat helpful.
Have a great day!