Need a formula
 

I can calculate the expiration date manually, but I need a formula. I use the information constantly but don't know how to set it up in excel.

John Doe's parole expires on 2/1/2008.
Each month on the 9th the parole date is reduced by 6 days.
If his parole expires before the 9th of the month, he will get 6 days on the 20th of the previous month.
When will John Doe's parole expire?
***the 6 represents a variable. It could be a 10, 12, 16 , 6****
The correct answer, calculated manually using the 6 is 12-27-2007.

//////////////////////////////////
The above does not work if I extend the start date to 2/1/2010 and change the compensation to 16.
The correct answer should be 9-26-2009.

Need a formula
 

Put these labels in the cells stated:

A1: Name
A2: Start date
A3: Compensation

A5: Calculated date

Then put these values in the cells stated:

B1: John Doe
B2: 2/1/2008 (1st Feb 2008)
B3: 6

Compensation days are the variable you referred to. Then enter this
formula in B5:

=IF(B2<=TODAY(),"Incorrect start date",B2-DATEDIF(TODAY(),B2,"m")*6-
(DAY(B2-DATEDIF(TODAY(),B2,"m")*6)<9)*B3)

and format the cell as a date. It returns 27th Dec 2007 with these
starting values.

I've not completely tested it with all possible start dates (i.e. to
check on the 9th of each month), but I hope this helps.

Pete

On Jul 24, 9:29 pm, old coach
wrote:
I can calculate the expiration date manually, but I need a formula. I
use the information constantly but don't know how to set it up in
excel.

John Doe's parole expires on 2/1/2008.
Each month on the 9th the parole date is reduced by 6 days.
If his parole expires before the 9th of the month, he will get 6 days
on the 20th of the previous month.
When will John Doe's parole expire?
***the 6 represents a variable. It could be a 10, 12, 16 , 6****
The correct answer, calculated manually using the 6 is 12-27-2007.

--
old coach