Calculating Due date based on multiple criteria

Closed
fixthemystery Posts 1 Registration date Friday April 11, 2014 Status Member Last seen April 11, 2014 - Apr 11, 2014 at 10:11 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 14, 2014 at 11:20 AM
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 September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 14, 2014 at 11:20 AM
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
0