| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vacation Spreadsheet- formula not working.
I need to track the accrued vacation time for each employee per their hire
date. I have a formula but it's not working. I need to track this per year, not a total of all the years. Here is the criteria and formula- *During 1st year vacation employee will accumulate at 3.34 a day for every month of service. (ie hire date = March 3, 2005- start accumulating April 3, 2005) *Starting on Jan 1 of the following date of hire = 80 hrs each year. *Starting on Jan 1 of the 10th year of service = 120 hrs each year. Formula for <=9 Years- =IF(AND(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)0,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)=9), 9,YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0))*80 =10 Years- =IF(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)9,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)-9,0)*120 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vacation Spreadsheet- formula not working.
You're asking someone to debug a mega formula without specifying
what the problem is. Does the formula return an error? An incorrect result? What is the expected result, with what data, and what is the actual result? You need to provide lots of detail about the nature of the problem, rather than simply saying "this massive formula doesn't work". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "naiveprogrammer" wrote in message ... I need to track the accrued vacation time for each employee per their hire date. I have a formula but it's not working. I need to track this per year, not a total of all the years. Here is the criteria and formula- *During 1st year vacation employee will accumulate at 3.34 a day for every month of service. (ie hire date = March 3, 2005- start accumulating April 3, 2005) *Starting on Jan 1 of the following date of hire = 80 hrs each year. *Starting on Jan 1 of the 10th year of service = 120 hrs each year. Formula for <=9 Years- =IF(AND(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)0,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)=9), 9,YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0))*80 =10 Years- =IF(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)9,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)-9,0)*120 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vacation Spreadsheet- formula not working.
Sorry. The massive formula returns an incorrect result. For <=9 Years
formula I should get a result of 80, rather I receive 720. For =10 Years I should get 120, rather I get 600. Hope this helps! "Chip Pearson" wrote: You're asking someone to debug a mega formula without specifying what the problem is. Does the formula return an error? An incorrect result? What is the expected result, with what data, and what is the actual result? You need to provide lots of detail about the nature of the problem, rather than simply saying "this massive formula doesn't work". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "naiveprogrammer" wrote in message ... I need to track the accrued vacation time for each employee per their hire date. I have a formula but it's not working. I need to track this per year, not a total of all the years. Here is the criteria and formula- *During 1st year vacation employee will accumulate at 3.34 a day for every month of service. (ie hire date = March 3, 2005- start accumulating April 3, 2005) *Starting on Jan 1 of the following date of hire = 80 hrs each year. *Starting on Jan 1 of the 10th year of service = 120 hrs each year. Formula for <=9 Years- =IF(AND(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)0,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)=9), 9,YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0))*80 =10 Years- =IF(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)9,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)-9,0)*120 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vacation Spreadsheet- formula not working.
Without any source data there is no way for us to effectively check why you
are getting the result you are. My suggestion would be to break the formula up into a bunch of intermidiate values (Calculate < 9 and 10 and ... in seperate columns and ensure that each cell is returning what it should. Then use a formula based on if statements and the intermediate values to arrive at the correct totals. Sometimes formulas get too big to effectively be debugged when something goes wrong. -- HTH... Jim Thomlinson "naiveprogrammer" wrote: Sorry. The massive formula returns an incorrect result. For <=9 Years formula I should get a result of 80, rather I receive 720. For =10 Years I should get 120, rather I get 600. Hope this helps! "Chip Pearson" wrote: You're asking someone to debug a mega formula without specifying what the problem is. Does the formula return an error? An incorrect result? What is the expected result, with what data, and what is the actual result? You need to provide lots of detail about the nature of the problem, rather than simply saying "this massive formula doesn't work". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "naiveprogrammer" wrote in message ... I need to track the accrued vacation time for each employee per their hire date. I have a formula but it's not working. I need to track this per year, not a total of all the years. Here is the criteria and formula- *During 1st year vacation employee will accumulate at 3.34 a day for every month of service. (ie hire date = March 3, 2005- start accumulating April 3, 2005) *Starting on Jan 1 of the following date of hire = 80 hrs each year. *Starting on Jan 1 of the 10th year of service = 120 hrs each year. Formula for <=9 Years- =IF(AND(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)0,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)=9), 9,YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0))*80 =10 Years- =IF(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)9,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)), DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)-9,0)*120 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ASSOCIATE VACATION TIME SPREADSHEET - EXEL | Excel Discussion (Misc queries) | |||
| Vacation accrual Spreadsheet | Excel Discussion (Misc queries) | |||
| Vacation Spreadsheet- | Excel Discussion (Misc queries) | |||
| Suggestions requested for vacation planning Excel spreadsheet | Excel Discussion (Misc queries) | |||
| spreadsheet that assigns vacation based upon seniority/time off a. | Excel Programming | |||
Linear Mode
