Conditional formatting for weekend doesn't work

S

Sacruzsa

New Member
Joined
Feb 28, 2022
Messages
29
Office Version
  1. 2019
Platform
  1. Windows
Hi everyone,
I'm having another problem with the calendar I'm developing for my shift, I'm trying to do conditional formatting on weekend days but no matter what I enter, nothing happens
used formulas =weekday($U4,2)>5 , here I always get an error message
when using ('certain text') nothing happens either, I have no idea what I'm doing wrong
As you can see the calendar is from left to right and not under each other , I have also merged 2 cells each time , I hope this is not the problem otherwise I have to start again from the beginning

1664189486993.png

Hopefully someone can help me (again) :)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
merged cells can always be a problem
Whats in U4 - a date , formatted as Day - OR text ?
if text then
=weekday($U4,2)>5
will not work
Also you are fixing the Column not the row

see in XL2BB - how i have used different formulas for real date v Text - you can apply those formulas to conditional formatting

Book1
ABCDEFGHI
1Dates formatted as DDDDSaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
2
3weekday(B1,2)>5TRUETRUEFALSEFALSEFALSEFALSEFALSETRUE
4
5
6
7dates as TextSaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
8
9weekday(B1,2)>5#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
10
11OR(B7="Saturday",B7="Sunday")TRUETRUEFALSEFALSEFALSEFALSEFALSETRUE
Sheet1
Cell Formulas
RangeFormula
B3:I3,B9:I9B3=WEEKDAY(B1,2)>5
B11:I11B11=OR(B7="Saturday",B7="Sunday")



I have added conditional formatting, using the formulas

Book1
ABCDEFGHI
1Dates formatted as DDDDSaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
2
3weekday(B$1,2)>5TRUETRUEFALSEFALSEFALSEFALSEFALSETRUE
4
5
6
7
8
9dates as TextSaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
10
11weekday(B$9,2)>5#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
12
13OR(B$9="Saturday",B$9="Sunday")TRUETRUEFALSEFALSEFALSEFALSEFALSETRUE
14
Sheet1
Cell Formulas
RangeFormula
B3:I3B3=WEEKDAY(B$1,2)>5
B11:I11B11=WEEKDAY(B$9,2)>5
B13:I13B13=OR(B$9="Saturday",B$9="Sunday")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:I5Expression=WEEKDAY(B$1,2)>5textNO
B10:I13Expression=OR(B$9="Saturday",B$9="Sunday")textNO
 
Upvote 0
Would this work for you?

22 09 26.xlsm
UVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
41/01/20232/01/20233/01/20234/01/20235/01/20236/01/20237/01/20238/01/202310/01/202312/01/2023
5AMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPM
Weekends
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U4:AN4Expression=WEEKDAY(LOOKUP(9^9,$U4:U4),2)>5textNO
 
Upvote 0
etaf said:
merged cells can always be a problem
Whats in U4 - a date , formatted as Day - OR text ?
if text then
=weekday($U4,2)>5
will not work
Also you are fixing the Column not the row

see in XL2BB - how i have used different formulas for real date v Text - you can apply those formulas to conditional formatting

Book1
ABCDEFGHI
1Dates formatted as DDDDSaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
2
3weekday(B1,2)>5TRUETRUEFALSEFALSEFALSEFALSEFALSETRUE
4
5
6
7dates as TextSaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
8
9weekday(B1,2)>5#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
10
11OR(B7="Saturday",B7="Sunday")TRUETRUEFALSEFALSEFALSEFALSEFALSETRUE
Sheet1
Cell Formulas
RangeFormula
B3:I3,B9:I9B3=WEEKDAY(B1,2)>5
B11:I11B11=OR(B7="Saturday",B7="Sunday")



I have added conditional formatting, using the formulas

Book1
ABCDEFGHI
1Dates formatted as DDDDSaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
2
3weekday(B$1,2)>5TRUETRUEFALSEFALSEFALSEFALSEFALSETRUE
4
5
6
7
8
9dates as TextSaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
10
11weekday(B$9,2)>5#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
12
13OR(B$9="Saturday",B$9="Sunday")TRUETRUEFALSEFALSEFALSEFALSEFALSETRUE
14
Sheet1
Cell Formulas
RangeFormula
B3:I3B3=WEEKDAY(B$1,2)>5
B11:I11B11=WEEKDAY(B$9,2)>5
B13:I13B13=OR(B$9="Saturday",B$9="Sunday")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:I5Expression=WEEKDAY(B$1,2)>5textNO
B10:I13Expression=OR(B$9="Saturday",B$9="Sunday")textNO
Click to expand...
Hi Etaf,

I have used both formulas, nothing works
Half the time I get an error message that the formula is not correct, the other time it accepts the formula but nothing happens, now I don't understand it at all.
U3 and V3 are merged and formatted as [$-en]dddd with a reference (=U4)
cells U4 and V4 are merged and formatted as date.
If you want I will share the file, so it might be easier to help me
 
Upvote 0
yes, share would be good
Did Peter_SSs formula work for you ?

I have always found merged cells when used in calculation to be an issue and try to avoid and use other methods for layout
 
Upvote 0
etaf said:
yes, share would be good
Did Peter_SSs formula work for you ?

I have always found merged cells when used in calculation to be an issue and try to avoid and use other methods for layout
Click to expand...
No, this one doesn't work either, I'll share the file hopefully it works for you

New SitPers met knoppen.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCD
1Sit Pers 23 Bn Med - TemplateToolsJanuary
2
3SundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesday
41/01/232/01/233/01/234/01/235/01/236/01/237/01/238/01/239/01/2310/01/2311/01/2312/01/2313/01/2314/01/2315/01/2316/01/2317/01/2318/01/2319/01/2320/01/2321/01/2322/01/2323/01/2324/01/2325/01/2326/01/2327/01/2328/01/2329/01/2330/01/2331/01/23
5AMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPM
6EMTAidmanRijb/Permis BRijb/Permis CUg8T - ASTRAVOLVOPIR-AIVDINGO-MPPVPANDURLXNX-LMVCLIMAXNEOSManitouGraad/GradeNaam/NomVoornaam PrénomStamnr MatriculeDienst/SvFunctie Fonction
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Sitpers 2023
Cell Formulas
RangeFormula
U3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3,BK3,BM3,BO3,BQ3,BS3,BU3,BW3,BY3,CA3,CC3U3=U4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U6:AQ23Expression=WEEKDAY(LOOKUP(9^9;$U4:U4);2)>5textNO
X20Expression=" =WEEKDAY(U3,2)>5"textNO
 
Upvote 0
Thanks for the XL2BB. When using it, just select the relevant range so it isn't too big. eg For this, you could start at column U.

Cells W3:AQ contain text, not formulas referencing the date below. If we fix that ..

22 09 26.xlsm
UVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCD
1January
2
3SundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesday
401-01-2302-01-2303-01-2304-01-2305-01-2306-01-2307-01-2308-01-2309-01-2310-01-2311-01-2312-01-2313-01-2314-01-2315-01-2316-01-2317-01-2318-01-2319-01-2320-01-2321-01-2322-01-2323-01-2324-01-2325-01-2326-01-2327-01-2328-01-2329-01-2330-01-2331-01-23
5AMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPM
Weekends2
Cell Formulas
RangeFormula
U3,W3,Y3,AA3,AC3,AE3,AG3,AI3,AK3,AM3,AO3,AQ3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3,BK3,BM3,BO3,BQ3,BS3,BU3,BW3,BY3,CA3,CC3U3=U4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U3:CD4Expression=WEEKDAY(LOOKUP(9^9,$U3:U3),2)>5textNO
 
Upvote 0
Solution
Edit - I will leave with Peter_SSs
to avoid any confusion

Peter_SSs formula works
Note U3&V3 show a date , W3&X3 shows text
So those cells ahould be fixed - BUT Peter

I have just added the formula
but i also have not selected your first merged Cell - which will highlight it all

modified the formula to
=WEEKDAY(LOOKUP(9^9,$U$4:U$4),2)>5
and selecting
$U$3:$CD$10 as the range to conditional format

test 1.xlsx
UVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ
344927MondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursday44939449404494144942
444927449284492944930449314493244933449344493544936449374493844939449404494144942
5AMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPM
6
7
8
9
10
11TRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSETRUETRUETRUETRUE
12
Sheet3
Cell Formulas
RangeFormula
U3,AS3,AU3,AW3,AY3U3=U4
U11:AJ11U11=WEEKDAY(LOOKUP(9^9,$U4:U4),2)>5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U3:CD10Expression=WEEKDAY(LOOKUP(9^9,$U$4:U$4),2)>5textNO
 
Upvote 0
etaf said:
Edit - I will leave with Peter_SSs
to avoid any confusion

Peter_SSs formula works
Note U3&V3 show a date , W3&X3 shows text
So those cells ahould be fixed - BUT Peter

I have just added the formula
but i also have not selected your first merged Cell - which will highlight it all

modified the formula to
=WEEKDAY(LOOKUP(9^9,$U$4:U$4),2)>5
and selecting
$U$3:$CD$10 as the range to conditional format

test 1.xlsx
UVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ
344927MondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursday44939449404494144942
444927449284492944930449314493244933449344493544936449374493844939449404494144942
5AMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPMAMPM
6
7
8
9
10
11TRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSETRUETRUETRUETRUE
12
Sheet3
Cell Formulas
RangeFormula
U3,AS3,AU3,AW3,AY3U3=U4
U11:AJ11U11=WEEKDAY(LOOKUP(9^9,$U4:U4),2)>5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U3:CD10Expression=WEEKDAY(LOOKUP(9^9,$U$4:U$4),2)>5textNO
Click to expand...
Whatever I enter or adjust, I always get the message below, never seen or experienced what's wrong with this
1664198224797.png

What am I doing wrong, I don't know anymore
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
356
Romy
R
A
  • Question
Conditional formating (with range) but instead of the color/formating of the certain cell to get values inside the matching cells in the range?
Replies
1
Views
205
aircarrier
A
Ramballah
  • Question
Huge nested IF or is there a simpler way?
Replies
4
Views
218
Ramballah
Ramballah
M
  • Question
Fantasy Calendar Creation stumbling..
Replies
2
Views
177
m26e4u
M
S
  • Solved
Conditional Format to change cell depending on date
Replies
2
Views
270
still learning
S

Forum statistics

Threads
1,215,560
Messages
6,125,523
Members
449,236
Latest member
Afua

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top