Need a formula

O

old coach

I can calculate the expiration date manually, but I need a formula.
use the information constantly but don't know how to set it up i
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 day
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
 
P

Pete_UK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top