Formula Help Required

pebzarolla

New Member
Joined
Oct 27, 2009
Messages
6
Hi forum members, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I am trying to create a spreadsheet that determines due dates for assessments that are sent out for review and also determines whether the assessments were completed on time based on priority.<o:p></o:p>
So far I have been able to successfully complete the following (taking into account both working days (M-F) and holidays (defined in another column))<o:p></o:p>
Column A = Date Issued<o:p></o:p>
Column B = Date Assessment response required (This is calculated using the following formula: =WORKDAY(A5,3, Holidays))<o:p></o:p>
Column C = Date Assessment Received (Manual entry of date)<o:p></o:p>
Column D= Determines whether response received in time (This is calculated using the following formula: =IF(Cn>Bn,"No","Yes")<o:p></o:p>
<o:p></o:p>
I now need to add an additional column that allocates an assessment priority, this being Priority 1 = 1 Working Day, Priority 2 = 2 Working Days, and Priority 3 - 3 Working Days.<o:p></o:p>
On this basis the Response required and response received columns need to reflect the priority of the assessment.<o:p></o:p>
<o:p></o:p>
Any help greatly appreciated<o:p></o:p>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Welcome to the MrExcel Board!

Something like this?

Excel Workbook
ABCDE
1Date IssuedPriorityDate RequiredDate ReceivedIn Time
222/10/2009226/10/200927/10/2009No
Date Required
 
Upvote 0
Many thanks for the prompt response, I can't believe it was as easy as that to work out!
Grateful of your assistance
 
Upvote 0
Following on from my original query, if I need to amend the number of days associated to my priorites how is this reflected in the formula.
I need to amend Priorities as follows:

Priority 1 = 1 Working Day
Priority 2 = Between 2 & 7 working days
Priority 3 = 7 Working Days

Any help greatly appreciated.
 
Upvote 0
Priority 2 = Between 2 & 7 working days
I presume this means that the 'Date Required' you would want displayed is 6 working days after 'Date Issued'. In that case, with the previous layout, try this in C2

=WORKDAY(A2,CHOOSE(B2,1,6,7),Holidays)
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top