Payroll Forecast Calc

[Steve]

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!!

 

[Steve]

Sorry, forgot an area...only posted 4. The 5 again are:
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!

 

[joeu2004]

Sorry, forgot an area...only posted 4.  The 5 again are:
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.

 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.