Help with Bank Holidays and Out of Hours

[Mchgh9]

Hi,

I already have a function which looks at the date and works out what day of the week and by looking at the time of issue, works out if it is between business hours or out of hours.

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=383 border=0><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 23.25pt" height=31><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: gray 1pt solid; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" width=106 height=31>Actual Start Time</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: gray 1pt solid; BACKGROUND-COLOR: transparent" width=95>Actual Start Date</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: gray 1pt solid; BACKGROUND-COLOR: transparent" width=90>Day of Week</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 69pt; BORDER-BOTTOM: gray 1pt solid; BACKGROUND-COLOR: transparent" width=92>Out of Hours</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=106 height=17>13:00</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=95>02/01/2011</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sunday</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>TRUE</TD></TR></TBODY></TABLE>

The day of the week is worked out from;

=TEXT(WEEKDAY(AD31), "dddd")

Out of Hours is worked out from;

=OR(WEEKDAY(AD31,2)>5,AC31<8/24,AC31>16.5/24)

The only problem is that although I can use this data to work out weekends and out of business hours (08:00-16:30), it does not take bank holidays into account.

Can anyone suggest a way around this please?

Thanks

David

 

[Jonmo1]

It's not exactly clear what you want to do.
What result do you expect to recieve if a date is a Holiday?

You can use the Workday function to exclude weekends and holidays.
It requires the analysis toolpack from tools - addins.
If you're in XL2007 or higher, it's installed by default.


Note, this formula isn't correct
=TEXT(WEEKDAY(AD31), "dddd")

The weekday function is returning a number from 1 to 7.
Excel stores dates as serial numbers.
1 = Jan 1 1900
2 = Jan 2 1900
3 = Jan 3 1900

It's a coincidence that your formula produces the desired result, but it is not exactly correct.
It just happens that Jan 1 1900 is a Sunday
So it appears your formula is giving the correct result..

You should actually only use
=TEXT(AD31, "dddd")

 

[Mchgh9]

Thanks for your reply.

If it is a bank holiday I want to omit it from my results, I want it to be included with the data for out of business hours and weekends.

If my formula is incorrect then how come it seems to be correct for exactly single date over 2,000 entries?

Thanks again.

 

[Jonmo1]

Thanks for your reply.

If it is a bank holiday I want to omit it from my results, I want it to be included with the data for out of business hours and weekends.

You'll have to create a range that contains bank holiday dates.
Then you can use match to see if the given date exists in that range.

=ISNUMBER(MATCH(AD31,F1:F20,0))
F1:F20 = range of holiday dates

This returns TRUE if the date is a holiday.


Yes, you have to manually enter the holiday dates yourself.
Excel doesn't store them anywhere, because there are too many variables.
What one business considers a holiday is not the same as the next business...

 

[Jonmo1]

Thanks for your reply.

If my formula is incorrect then how come it seems to be correct for exactly single date over 2,000 entries?

For the reason I provided...
It's a concidence that it produces the desired result.

Weekday is returning a number from 1 to 7 based on Sun Mon Tue etc..

The TEXT function is then testing THat number, 1 to 7.
the text function is converting that number (1-7) to a real excel Date
1 = January 1 1900 - it just happens to be a Sunday.
That's why it gives the desired result.


You only need the =Text(AD31,"dddd")

See below
Column A is Dates, Jan 1 2011 - Jan 15 2011
Column B is a formula =Weekday(A1)
Column C is a formula =B1 (but formatted as a Date)

Excel Workbook
	A	B	C
1	1/2/2011	1	Sunday, January 01, 1900
2	1/3/2011	2	Monday, January 02, 1900
3	1/4/2011	3	Tuesday, January 03, 1900
4	1/5/2011	4	Wednesday, January 04, 1900
5	1/6/2011	5	Thursday, January 05, 1900
6	1/7/2011	6	Friday, January 06, 1900
7	1/8/2011	7	Saturday, January 07, 1900
8	1/9/2011	1	Sunday, January 01, 1900
9	1/10/2011	2	Monday, January 02, 1900
10	1/11/2011	3	Tuesday, January 03, 1900
11	1/12/2011	4	Wednesday, January 04, 1900
12	1/13/2011	5	Thursday, January 05, 1900
13	1/14/2011	6	Friday, January 06, 1900
14	1/15/2011	7	Saturday, January 07, 1900
Sheet1