Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payroll Forecast Calc
Hi all. I need to write a formula to calculate monthly payroll, based
on a hire date and termination date. The spreadsheet is sut up with the Months going across the top of the sheet (AA3:AL3), with the format being 1/1/2008, 2/1/2008, etc. Essentially, there are 5 areas within the IF statement: Hire date is lest than current month (no pay) Hire date is within current month, and must be prorated Hire date is after current month, and before termination date (full pay) Current month is after Termination date (no pay) Seems like this is a pretty standard type of payroll calc. I'm hoping that somene already has this massive formula written! Anyone? Thanks so much!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payroll Forecast Calc
Sorry, forgot an area...only posted 4. The 5 again a
1. Hire date is lest than current month (no pay) 2. Hire date is within current month, and must be prorated 3. Hire date is after current month, and before termination date (full pay) 4. Termination date is within current month, and must be prorated 5. Current month is after Termination date (no pay) Thanks! On Mar 12, 8:54*am, Steve wrote: Hi all. *I need to write a formula to calculate monthly payroll, based on a hire date and termination date. *The spreadsheet is sut up with the Months going across the top of the sheet (AA3:AL3), with the format being 1/1/2008, 2/1/2008, etc. *Essentially, there are 5 areas within the IF statement: Hire date is lest than current month (no pay) Hire date is within current month, and must be prorated Hire date is after current month, and before termination date (full pay) Current month is after Termination date (no pay) Seems like this is a pretty standard type of payroll calc. *I'm hoping that somene already has this massive formula written! *Anyone? *Thanks so much!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payroll Forecast Calc
On Mar 12, 6:59*am, Steve wrote:
Sorry, forgot an area...only posted 4. *The 5 again a 1. *Hire date is lest than current month (no pay) 2. *Hire date is within current month, and must be prorated 3. *Hire date is after current month, and before termination date (full pay) 4. *Termination date is within current month, and must be prorated 5. *Current month is after Termination date (no pay) I think you have the wrong cases. I believe the cases are (in the order specified): 1. Hire date is after the month. (No pay.) 2. Termination date is before the month. (No pay.) [Your #5.] 3. Hire date is after the first of the month. (Prorate.) 4. Termination date is before the end of the month. (Prorate.) 5. Otherwise, full pay. Note that #4 must also take #5 into account. That is: you must prorate based on the earlier of the termination date or the end of the month. Note that if #1 and 2 are false and #3 is true, the hire date must be in the current month. Similarly, if #1, 2 and 3 are false and #4 is true, the termination date must be in the current month. On Mar 12, 8:54*am, Steve wrote: *I'm hoping that somene already has this massive formula written! I could develop one, but I do not have one already written and tested. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calc = Manual & Do Not Calc b/4 SAVE | Excel Discussion (Misc queries) | |||
My calc key on Excel changes box to "Text" Box and doesn't calc ? | Charts and Charting in Excel | |||
Adding payroll stubs payroll calculator | New Users to Excel | |||
auto calc on, but have to edit (f2) cells to force re-calc..help! | Excel Worksheet Functions | |||
forecast | Excel Discussion (Misc queries) |