How to highlight cells which falls in working days and in office hours

[potlurul]

Hi Excelperts,

I have a column of data with time and date (format: 11/27/2009 12:42:40 PM) with hundreds of rows. I would like to highlight all the cells which fall with in the condition of Monday to Friday and 9:00am to 4:00pm, excluding public holidays. I am bit familiar using conditional formatting, but not sure how to use it for this case. Can anybody help on this?

Regards
potlurul

 

[anthonya2369]

If the date/time is in the same cell/column then this should work:

=AND(WEEKDAY(B1)>=2,WEEKDAY(B1)<=6,HOUR(B1)>=8,HOUR(B1)<=16)

Select the whole column, and input this in the area after selecting formula is. Keep note that it is setup for column B. If your column is A then change all the B1s to A1s.

Choose your highlighting and click ok.

 

[scottylad2]

i'm guessing your day and times are in different cells? When your not within the working hours, has your day to revert to unhighlighted?

 

[scottylad2]

ignore last post, never read op proper

 

[anthonya2369]

If the date/time is in the same cell/column then this should work:

=AND(WEEKDAY(B1)>=2,WEEKDAY(B1)<=6,HOUR(B1)>=8,HOUR(B1)<=16)

Select the whole column, and input this in the area after selecting formula is. Keep note that it is setup for column B. If your column is A then change all the B1s to A1s.

Choose your highlighting and click ok.

Sorry, the formula should be:

=AND(WEEKDAY(B1)>=2,WEEKDAY(B1)<=6,HOUR(B1)>=9,HOUR(B1)<16)

Keep in mind if it is 4:30 PM it will return as hour 16. So depending on how the time is set (at 4:00 PM will return 16 as well. However, 3:59 PM will show as hour 15.

 

[anthonya2369]

If you need holidays to be excluded, it gets real tricky and it would have to be tweaked using vb for the holiday portion. Let me know and I will gladly put together something but I would need to know the holidays (dates).

 

[anthonya2369]

Okay, in order to get holidays to be excluded, you need on your workbook through VB, to add a module and insert the following:

Function IsHoliday(ByVal dteInput As Date) As Boolean

On Error Resume Next

Dim Holidays(2)     As Date

dteInput = Format(dteInput, "mm/dd/yyyy")

Holidays(0) = "1/1/2009"
Holidays(1) = "1/18/2009"
Holidays(2) = "12/25/2009"

IsHoliday = False

For i = LBound(Holidays) To UBound(Holidays)
    If Holidays(i) = dteInput Then
        IsHoliday = True
    End If
Next i

End Function

Change:

Dim Holidays(2)

to from 2 to the actual number of holidays (less one since arrays start at 0 and not 1. So if there are 8 holidays to be excluded, change the 2 to 7.

Then add the addition Holiday lines (Holiday(3), Holiday(4) and set them equal to the holiday dates. Then change the conditional format to this:

=AND(WEEKDAY(B1)>=2,WEEKDAY(B1)<=6,HOUR(B1)>=9,HOUR(B1)<16,isholiday(B1)=FALSE)