Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to set up a formula that it adds value on a certain date
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to set up a formula that it adds value on a certain date
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 On Oct 15, 10:03 pm, LoriKLynn wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to set up a formula that it adds value on a certain date
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. "Pete_UK" wrote: 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 On Oct 15, 10:03 pm, LoriKLynn wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to set up a formula that it adds value on a certain date
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(E215,(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 "LoriKLynn" wrote: 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. "Pete_UK" wrote: 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 On Oct 15, 10:03 pm, LoriKLynn wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to set up a formula that it adds value on a certain date
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 "OssieMac" wrote: 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(E215,(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 "LoriKLynn" wrote: 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. "Pete_UK" wrote: 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 On Oct 15, 10:03 pm, LoriKLynn wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to set up a formula that it adds value on a certain date
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 "OssieMac" wrote: 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 "OssieMac" wrote: 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(E215,(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 "LoriKLynn" wrote: 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. "Pete_UK" wrote: 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 On Oct 15, 10:03 pm, LoriKLynn wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What formula adds 1 day to date in another workbook? | Excel Discussion (Misc queries) | |||
Date formula that adds 7 days to a cell when sheet is copied | Excel Worksheet Functions | |||
NEED A FORMULA THAT ADDS EVEN OR ODD ROW NUMBERS | Excel Worksheet Functions | |||
I wish to make a formula that adds dollars in 1 column if a date . | Excel Worksheet Functions | |||
Formula which adds together worksheets | Excel Discussion (Misc queries) |