Calculate Accrual Date

[Andy Cleveland]

I am creating a spreadsheet to keep track of time balances. We have three
types of balances to keep track of; Vacation, Sick, and Personal. A vacation
day is accrued each month on the anniversary date of the start date. A sick
day is accrued every 2nd, 6th, and 10th month after the start date and two
sick days and a personal day are accrued every 4th, 8th, and 12th month after
the start date.

For example, if the person started on Feb. 14th, then they would accrue a
vacation day each month on the 14th. They would accrue one sick day on April
14th, August 14th, and Dec. 14th. They would accrue two sick days and one
personal day on Jun 14th, Oct 14th, and (obviously) Feb 14th.

In my spreadsheet, I am calculating each day type (vacation/sick/personal)
separately. I have Column A filled with each day of the year.

My 1st question is how do I write a formula in Column B to say, "If the date
in Column A falls on the 14th then one vacation day is accrued?"

My 2nd question is how do a write a formula in Column C to say, "If the date
in this row in Column A falls on the 14th and the month falls on the 2nd,
6th, or 10th month after the start date, then one sick day is accrued but if
the date in this row in Column A falls on the 14th and the month falls on the
start date or the 4th, or 8th month after, then two sick days are accrued?"

I presume I can figure out the fomula for the personal day accrual based on
the sick day formula.

 

[smartin]

I am creating a spreadsheet to keep track of time balances. We have three
types of balances to keep track of; Vacation, Sick, and Personal. A vacation
day is accrued each month on the anniversary date of the start date. A sick
day is accrued every 2nd, 6th, and 10th month after the start date and two
sick days and a personal day are accrued every 4th, 8th, and 12th month after
the start date.

For example, if the person started on Feb. 14th, then they would accrue a
vacation day each month on the 14th. They would accrue one sick day on April
14th, August 14th, and Dec. 14th. They would accrue two sick days and one
personal day on Jun 14th, Oct 14th, and (obviously) Feb 14th.

In my spreadsheet, I am calculating each day type (vacation/sick/personal)
separately. I have Column A filled with each day of the year.

My 1st question is how do I write a formula in Column B to say, "If the date
in Column A falls on the 14th then one vacation day is accrued?"

My 2nd question is how do a write a formula in Column C to say, "If the date
in this row in Column A falls on the 14th and the month falls on the 2nd,
6th, or 10th month after the start date, then one sick day is accrued but if
the date in this row in Column A falls on the 14th and the month falls on the
start date or the 4th, or 8th month after, then two sick days are accrued?"

I presume I can figure out the fomula for the personal day accrual based on
the sick day formula.

Hi Andy,

You might try starting with a few helper columns to sort it out.

With dates in A1, and the start date in some cell named "Start",

In B:
=DATEDIF(Start,A4,"m") will return the integer number of months from
Anniversary to the date in A4. This "little secret" function is well
documented on Chip Pearson's site:
http://www.cpearson.com/excel/datedif.aspx

In C:
=AND(B4>0,DAY(A4)=DAY(Start)) will return TRUE on each monthly
anniversary date.

In D:
=MOD(B4-2,4)=0 will return TRUE if this is month 2, 6, 10, etc.

In E:
=AND(B4>0,MOD(B4,4)=0) will return TRUE if this is month 4, 8, 12, etc.

Then look to columns C, D, and E to determine whether you should accrue
time.

Now, there will be a problem if the Anniversary date is the 29th, 30th,
or 31st of the month, because these values do not occur in every month
of every year. Not knowing your business rule here, suggestions are you
could adjust the anniversary date either back to the 28th, or forward to
the 1st of the next month.