Expiry Date with Specific criteria

[caslan]

Hi

Please can you assist. I have a report and need to calculate the expiry date based on what the outcome was. Each outcome has its own length before it expires. The expiry date should start calculating from the date is was finalized.
Example:
Final written warning expires after 12 months
Dismissal is immediate
1st Level warning after 6 months

I have attached the sheet as well.

Any assistance would be greatly appreciated.

DATE OF ACTION DATE FINALISED CATEGORY OF OFFENCE TRANSGRESSION OUTCOME MONTH Expiry Date
2018-12-11 2019-01-08 Substance Abuse Under the influence Final Written Warning Dec-18
2018-12-11 2018-12-11 Substance Abuse Under the influence Final Written Warning Dec-18
2018-12-11 2018-12-18 Negligence Absconded Dismissal Dec-18
2018-12-21 2019-01-03 Attendance Reporting late for duty 03-12-2018 1st Level Warning Dec-18

[Glenn Kennedy]

Please list ALL criteria and correspnding expiry date.

[Glenn Kennedy]

Based on the incomplete criteria:

=IF([@OUTCOME]="","",IFERROR(EDATE([@MONTH],VLOOKUP([@OUTCOME],W:X,2,FALSE)),[@MONTH]))

see sheet.

[caslan]

Hi Glenn

Please see attached all criteria and expiry date, I have looked at what you have given me, however I have added on the sheet what I need.
Formula Based on Action date, if criteria is "1st level warning" and expires in 3 months, what would the date be. The expiry date on the sheet is not on my report, should this be added to the report or put on a hidden sheet?

Thank you for your assistance.

[bebo021999]

How about in N2:

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl see the file. Note the changes in Criteria Sheet column C and D.

In P2 then copied down.

=[@[DATE FINALISED]]+INDEX(Criteria!$C$2:$C$14,MATCH([@OUTCOME],Criteria!$B$2:$B$14,0))

[caslan]

Hi

Thank you, unfortunately it is not calculating correctly. Maybe I am doing something wrong. I will give it another try after work.

Thank you

[kvsrinivasamurthy]

Sorry there was mistake in the formula . It is corrected here

=EDATE([@[DATE FINALISED]],INDEX(Criteria!$C$2:$C$14,MATCH([@OUTCOME],Criteria!$B$2:$B$14,0)))

[caslan]

Hi

A very big thank you, worked like a charm.

[kvsrinivasamurthy]

Thanks for feed back. Pl mark the thread solved.