Count between Dates and exclude bank holidays

[Steven1985]

Hi,

I need to use a formula to count SLA turnarounds on my spreadsheet. I have created a formula below to calculate the expected due date.

On the tab 'Alert Spreadsheet' the following columns are populated

Column AH3 is Data Alert received
Column AM3 is SLA (the cell this formula is in)

=IF(AH3="","",SUM(AH3+1))

This works fine but need bank holidays and weekends to be excluded.

I have on a sep tab named 'Controls' which lists all the days in they year by date and day, I have the formula today() and a sep box with the remaining bank holidays.

I think i need to use NETWORKDAYS but I cant get the formula to work. Can anyone help?

Thanks
Steve

 

[Joe4]

Take a look here for examples on how to set-up NETWORKDAYS.
For the holidays, it is best to create a list/table of holidays to exclude, and reference that range in your formula.
http://office.microsoft.com/en-us/excel-help/networkdays-HP005209190.aspx

 

[Steven1985]

The problem I have is linking the 2 formulas together -

=IF(AH3="","",SUM(AH3+1)) plus the networkdays, ive tried various formulas but cant grasp it.

 

[Joe4]

I am not quite clear, I think there are a bunch of things we need to clear up.

Can you post a small sample of what is in AH3 and AM3?
Should AH3 read "Date Alert received" instead of "Data Alert received"?
What exactly is in AM3 (I do not know what "SLA" is)? Is it a date?

What is this supposed to?
SUM(AH3+1)
If you just want to add one to AH3, SUM is not necessary. SUM is used to add up a whole range of numbers, not to do simple addition.

Rather than trying to decipher your formulas, it might be best to just take a step back, and post a small sampling of your data and just explain in English exactly what you want.

 

[Steven1985]

SLA is a Service Level Agreement.
What im saying is, Column AH3 is Date Alert received and Column
AM3 is SLA.

If a date in Date alert received in populated i.e 25/08/2011 then the formula used =IF(AH3="","",SUM(AH3+1)) in the SLA fields will tell me the date the Alert should be completed. As below.
<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=99><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 53.25pt; mso-height-source: userset" height=71><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; WIDTH: 74pt; HEIGHT: 53.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl67 height=71 width=99>Alert Generated Date</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=21 align=right>26/08/2011</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 72pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=96><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 53.25pt; mso-height-source: userset" height=71><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 72pt; HEIGHT: 53.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=71 width=96>PRT Alert SLA</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl68 height=21>27/08/2011</TD></TR></TBODY></TABLE>

I only need this to count working days (Mon - Fri)

Our SLA is 2 days, The 26th is 1 day the 27th is the 2nd day.

The example above is a good one to use. So Date alert received 26/08/2011 Friday (1 day) Saturday and Sunday doesnt count as a non working day and Monday 29th was a bank holiday so the PRT SLA should populate 30th August (as this is the next working day) but my formula shows the 27th (Saturday). Does this make more sense?

Thanks

 

[Joe4]

Yes. Actually, it is not NETWORKDAYS you want (which calculates the number of workdays between two days), but WORKDAY (which calculates the date a set number of workdays from a beginning date).

So I think you want something like this in AM3:
=WORKDAY(AH3,1,Holidays)
where Holidays is your the range containing your list of holidays.

 

[Steven1985]

Thats great that works.

How do I keep Column AM3 Blank is Column AH isnt populated?

Thanks

 

[barry houdini]

You can add an IF fuction to Joe's suggested formula, e.g.

=IF(AH3="","",WORKDAY(AH3,1,Holidays))