Formula Help Required

[pebzarolla]

Hi forum members, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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></o>
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></o>
Column A = Date Issued<o></o>
Column B = Date Assessment response required (This is calculated using the following formula: =WORKDAY(A5,3, Holidays))<o></o>
Column C = Date Assessment Received (Manual entry of date)<o></o>
Column D= Determines whether response received in time (This is calculated using the following formula: =IF(Cn>Bn,"No","Yes")<o></o>
<o></o>
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></o>
On this basis the Response required and response received columns need to reflect the priority of the assessment.<o></o>
<o></o>
Any help greatly appreciated<o></o>

 

[Peter_SSs]

Welcome to the MrExcel Board!

Something like this?

Excel Workbook
	A	B	C	D	E
1	Date Issued	Priority	Date Required	Date Received	In Time
2	22/10/2009	2	26/10/2009	27/10/2009	No
Date Required

 

[pebzarolla]

Many thanks for the prompt response, I can't believe it was as easy as that to work out!
Grateful of your assistance

 

[pebzarolla]

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.

 

[Peter_SSs]

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)

 

[pebzarolla]

Once again really appreciate your assistance, this is exactly what I was looking to calculate.

Many Thanks