Forum Discussion

Neil Land's avatar
Neil Land
Copper Contributor
Jul 11, 2018

Excel formula help

Hello,

 

I am attempting to create a formula that will automatically calculate a set number of days from a specified date based upon the urgency of a call. For example:

  • If I receive a “Priority 1” call, I have 4 ACTUAL days to complete the job from the call-in date. 
  • If I receive a “Priority 2” call, I have 7 BUSINESS days to complete from call-in date.
  • If I receive a “Priority 3” call, I have 30 BUSINESS days to complete from call-in date.

I wish to use one cell to add the call-in date, another cell with a drop down list (P1, P2, P3), and the final cell to show the completion date based upon the above criteria. Would I use the SWITCH formula to achieve this? If so, how do I do it?

admin
developer
excel
Excel on mobile
Excel Online
Formulas and Functions
office 365
training

4 Replies

Sort By
  • pranav trikha's avatar
    pranav trikha
    Brass Contributor
    Jul 21, 2018
    Greetings,
    Pls find formula
    =SWITCH(PType,1,WORKDAY(B3,4,Hollist1),2,WORKDAY(B3,7,Hollist1),3,WORKDAY(B3,30,Hollist1))
    Hope it helps.
    Thanks
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 21, 2018

      With comment what for Priority 1 these are calendar days, not business one. Thus B3+4 or so, depends on receiving call date is included or not.

      And perhaps values shall be "P1", "P2","P3" or so

      • pranav trikha's avatar
        pranav trikha
        Brass Contributor
        Jul 21, 2018

        Attached file for reference, the dates added to call dates as per specs provided and holidays excluding, (in the example file attached, Sat and Sun are taken as weekly off, including 1st Jan, other holidays can as well be included, thus arriving at business days (4,7,30) to be added to Initial call date.

         

         

Resources