CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
benryon
benryon

Reputation: 1

Conditionally format in Excel cells of dates & times based on if it's between certain hours of the day and days of the week

I'm working on a spreadsheet that displays different times of the day across time zones based on the current time in UTC/GMT. I have a field of cells that have up-to-date hours of the day, but I'd like to conditionally highlight them if they fall between working hours (8 am to 5 pm) and Monday through Friday.

I thought this was the formula to past into the conditional formatting rule:

=AND(AND((HOUR($B8)>7),(HOUR($B8)<17)),(DAY($B8)>=1),(DAY($B8<6)))

But it's not highlighting properly. I'm not very knowledgeable in this level of formulas and would love any help.

Upvotes: 0

Views: 189

Answers (1)

Ike
Ike

Reputation: 13044

You have to use WEEKDAY - it returns 1 for Mondy, 2 for Tuesday etc. - if you choose 2 as second parameter:

=AND(HOUR($B8)>7,HOUR($B8)<17,WEEKDAY($B8,2)<6)

DAY returns the day of the date, e.g. 16.1.2024 --> Day = 16.

Upvotes: 0

Related Questions