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

potlurul

New Member
Joined
Sep 14, 2009
Messages
4
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

Active Member
Joined
Mar 10, 2005
Messages
321
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

Well-known Member
Joined
Feb 2, 2009
Messages
1,915
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?
 

anthonya2369

Active Member
Joined
Mar 10, 2005
Messages
321
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:
Code:
=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

Active Member
Joined
Mar 10, 2005
Messages
321
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

Active Member
Joined
Mar 10, 2005
Messages
321
Okay, in order to get holidays to be excluded, you need on your workbook through VB, to add a module and insert the following:

Code:
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:

Code:
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)
 

Forum statistics

Threads
1,082,548
Messages
5,366,227
Members
400,880
Latest member
dwb

Some videos you may like

This Week's Hot Topics

Top