Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. Last edited by old coach : February 27th 09 at 03:05 AM Reason: won't work with changes. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|