1. jk
Member
Join Date
Dec 1969
Posts
86

I would like to find out whether the given date&time is a business hour(7 am to 7pm) which should also consider holidays and weekends. For holidays, i have a table which contains all the holiday dates.<BR><BR>Any links or help for this will be greatly appreciated.<BR><BR>Thanks,

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Not hard...

&#060;%<BR>Function isBusinessHour( testDateTime )<BR> &#039; if outside of times, no need to check day...<BR> theHour = Hour( testDateTime )<BR> &#039; this allows 7:00 AM to 6:59 PM...if you really want to<BR> &#039; check for exactly 7 PM, we can do that...<BR> If theHour &#060; 7 OR theHour &#062; 18 Then<BR> isBusinessHour = False<BR> Exit Function<BR> End If<BR> &#039; if it&#039;s a weekend, no need to check holidays...<BR> wkday = Weekday( testDateTime )<BR> If wkday = 1 OR wkday = 7 Then<BR> isBusinessHour = False<BR> Exit Function<BR> End If<BR> &#039; then here is where you&#039;ll have to check your table of holidays<BR> &#039; you can use any or all of the lines below to help you:<BR> theYear = Year( testDateTime )<BR> theMonth = Month( testDateTime )<BR> theDay = Day( testDateTime )<BR> theDate = DateValue( testDateTime )<BR> &#039; for example, if you are going to check against a database<BR> &#039; that has every holiday in each year listed separately, then<BR> &#039; you would only need theDate and you&#039;d do:<BR> SQL = "SELECT * FROM holidays WHERE holidayDate=#" & theDate & "#"<BR> Set RS = yourConnection.Execute( SQL )<BR> isBusinessHour = RS.EOF &#039; true if date not a holiday...sneaky?<BR> RS.Close<BR>End Function<BR>%&#062;<BR><BR>

3. jk
Member
Join Date
Dec 1969
Posts
86

## RE: Not hard...

Working great. Thanks, what changes do i need to make to the function if the business hours change to something like 6:30AM to 5:30PM ?<BR><BR>Thanks again....

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Probalby should have done it...

...this way, in the first place.<BR><BR>TimeSerial function builds up a time in the form<BR> TimeSerial( thehour, theminute, thesecond )<BR>TimeValue function works like DateValue: It extracts *only* the time from a date/time value. (Where DateValue extracts only the date, of course.)<BR><BR>***********<BR><BR>&#060;% <BR>Function isBusinessHour( testDateTime ) <BR> &#039; if outside of times, no need to check day... <BR> theTime = TimeValue testDateTime )<BR> &#039; accept from 7:00 AM to 7:00 PM (19:00:00 in universal time format)<BR> If theTime &#060; TimeSerial(7,0,0) OR theTime &#062; timeSerial(19,0,0) Then<BR> isBusinessHour = False <BR> Exit Function <BR> End If <BR> &#039; if it&#039;s a weekend, no need to check holidays... <BR> wkday = Weekday( testDateTime ) <BR> If wkday = 1 OR wkday = 7 Then <BR> isBusinessHour = False <BR> Exit Function <BR> End If <BR> &#039; then here is where you&#039;ll have to check your table of holidays <BR> &#039; you can use any or all of the lines below to help you: <BR> theYear = Year( testDateTime ) <BR> theMonth = Month( testDateTime ) <BR> theDay = Day( testDateTime ) <BR> theDate = DateValue( testDateTime ) <BR> &#039; for example, if you are going to check against a database <BR> &#039; that has every holiday in each year listed separately, then <BR> &#039; you would only need theDate and you&#039;d do: <BR> SQL = "SELECT * FROM holidays WHERE holidayDate=#" & theDate & "#" <BR> Set RS = yourConnection.Execute( SQL ) <BR> isBusinessHour = RS.EOF &#039; true if date not a holiday...sneaky? <BR> RS.Close <BR>End Function <BR>%&#062; <BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•