How to set up a formula that it adds value on a certain date

G

Guest

I am trying to develop an Earned Leave form for my company that each employee
can utilize for his/her own purpose. I need to create a formula for sick
leave. We accrue 8 hours per month on the 16th day of each month.

For example, I would like to create a formula for "Jane Doe". She began
working on August 1, 2007. On August 16th, the formula adds 8 hours to
Jane's Sick Leave balance. On September 16, it adds an additional 8 hours
for a total of 16 hours of Sick Leave balance, etc. On October 27th, Jane
will be out of the office on sick leave for 3 hours. In another cell, she
will post the 3 hours. In the first cell where the formula is calculating
the 8 hour accruals, Jane can also request the that the formula also subtract
any value posted the second cell. Therefore, Jane's total Sick Leave balance
as showing in the cell would be 21 hours of sick leave remaining (8 hours on
August 16th, 8 hours on September 16th, 8 hours on october 16th, minus 3
hours on October 27th).

If this makes sense to anyone, I would greatly appreciate ascertaining if
this is possible. Thank you.
 
P

Pete_UK

It would be helpful to know the exact columns that you are using.
Presumably you have a column for name and start-date. How do you
intend to cope with Jane's second sickness of 1 day (7 hours ??) in
November? Will she just add those hours to the 3 she has already
recorded for October, or will she have a column for each month to
record the sickness in that month? Is it important to record the date
the period of sickness started (or ended?)?

Pete
 
G

Guest

Thank you for your response.

To answer your question, each time Jane adds an amount, if she wants to keep
a record of it, she can open up another worksheet (e.g., October 27th: 3
hours, November 3rd: 8 hours etc.); otherwise what she would essentially be
doing is overwriting the data in the cell.

For example, when she enters the 3 hours for October 27th, the formula will
show a 21-hour balance. She saves the data and closes the document. On
November 3rd, she opens the document and overwrites the 3 hours from October
27th with the 8 hours from November 3rd. But, the "Sick Leave" balance cell
will be starting with the 21 hours she saved when she went in on October
27th. When she inputs the 8 hours for Nov. 3rd, the balance is now 13 hours.
She saves and closes the document. If she should take another sick day on
say, November 12th, she would be starting at the 13-hour balance. On
November 16th, the formula will again add 8 hours for the month.

Hope that helps. I know it sounds a little confusing.
 
G

Guest

Hi Lori,

I see that someone else is also working on this but since I have done the
work then I might as well post it.

The following will give you the principles involved. You may want to nest
some of the formulas and not have so many columns but if I do that it makes
it very difficult for you to understand.

Insert the following column headers in columns cells A1 to I1:-

Employee, Date commenced, Today today, Full yrs service, Day of month,
Months service, Accrued leave (Hrs), Leave taken (Hrs), Leave balance.

Insert the following in the cells shown:-

A2: Jane Doe
B2: August 1, 2007
C2: =TODAY()
D2: =YEAR(C2)-YEAR(B2)
E2: =DAY(C2)

The following is one line to insert in cell F2:-
=IF(E2>15,(YEAR(C2)-YEAR(B2))*12*D2+MONTH(C2)-MONTH
(B2),(YEAR(C2)-YEAR(B2))*12*D2+MONTH(C2)-MONTH(B2)-1)

G2: =F2*8+8
H2: 3
I2: =G2-H2

If after creating the worksheet as above, you want to keep all the columns
but not necessarily in view then if you cut a column and insert the cut cells
somewhere over the right out of sight then the formulas will 'heal'
themselves with the new references.

Regards,

OssieMac
 
G

Guest

Hi again Lori,

Change the formula in G2 to this:-

=IF(DAY(B2)<16,F2*8+8,F2*8)

I hope that I assumed correctly that if an employee starts before the
accrual day of 16th then they still get the 8 hrs on the 16th even if they do
not have a full months service. If this is not correct then please get back
to me.

The above correction is to take into account whether an employee starts
between 1st day of month and 15th day or between 16th and last day of month.

regards,

OssieMac
 
G

Guest

Yet another correction or really an omission this time.

Format cells D2 to I2 as numeric with no decimal places. It won't affect the
calculations but some of them might display in date format otherwise.

You also might also want to format B2 and C2 to date format but as general
format they will display dates entered as such anyway.

I hope third time lucky and I have it right now.

Regards,

OssieMac
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top