Calculating Due date based on multiple criteria

Closed
fixthemystery Posts 1 Registration date Friday 11 April 2014 Status Member Last seen 11 April 2014 - 11 Apr 2014 à 22:11
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 14 Apr 2014 à 11:20
Criteria

3 products, choose one classification, one category, all time frames are different

Products: HBX, Flex, NYM
Categories: Complaint, Gvnc, Appl, Cmp Appl
Urgency: Standard, Expedited, Immediate,
Classification: Service (30 days), Payment (60 calendar days), standard, Post Service, All other
Urgency Defined: Standard 60 days to fix, Expedited 3 days to fix, Immediate 7 days to fix, Post Service 45 calendars, Pre Service 35 business days
Receipt date: 4/11/14

so the due date is calculated from the received date and the chosen categories,
If HBX, Grievance, Post Service, then the receipt date 4/11/14 gives a due date of
4/11/14 + 45 days, excel reports the due date.

Example 2:

If NYM, Complaint, Standard, Service then the due date will be calculated from the receipt date of 4/11/14 + 60 calendar days.

Example 3 Flex, Payment, Standard, the due date = the receipt date of 4/11/14 + 60 days

Example 4: NYM Complaint appeal, standard 30 business days from 4/11/14. How can excel calculate the due date

1 response

TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
14 Apr 2014 à 11:20
Hi Fixthemystery,

My suggestion would be to create a table for your number of days:
A2: Post Service B2: 45
A3: Standard B3: 60

And then use VLOOKUP to retrieve your data and add it up to the receipt date.

Best regards,
Trowa