Conditional format shift pattens

V

VeryNoviceExcelUser

New Member
Joined
Jan 28, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,
Firstly I am new to this forum so please be nice.

I am trying to create a spreadsheet that uses conditional formatting to highlight a 4 on 4 off shift patten over a 12 month period.

For example, Joe works:
Week 1 Mon, Tue, Wed and Thur.
Week 2 Tue, Wed, Thur & Fri .
Week 3 Wed, Thur, Fri & Sat.
Week4 Thur, Fri, Sat & Sun. And so on.

Is this possible? If yes I would assume it is some formula I would have to use but not sure which one.

Any and all help will be of great help.

Thanks.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Hello VeryNoviceExcelUser

Welcome to the forum. I encourage you to read the forum rules to ensure you create a thread that has the best change to get an excellent solution. There is a link in my signature thereto.

Yes, it would be a formula, but it depends on your spreadsheet set-up how it would work.
For example, is there anybody else then Joe working? How would that work when there are overlapping schedules? That becomes very rapidly unreadable.

I guess you want something of this sort?

EDIT: Note this is not ideal, since it's not flexible with other years, I just noticed myself. But I do not have time anymore to work on it.

Book1
CDEFGHIJ
211/01/20242/01/20243/01/20244/01/20245/01/20246/01/20247/01/2024
328/01/20249/01/202410/01/202411/01/202412/01/202413/01/202414/01/2024
4315/01/202416/01/202417/01/202418/01/202419/01/202420/01/202421/01/2024
5422/01/202423/01/202424/01/202425/01/202426/01/202427/01/202428/01/2024
6529/01/202430/01/202431/01/20241/02/20242/02/20243/02/20244/02/2024
765/02/20246/02/20247/02/20248/02/20249/02/202410/02/202411/02/2024
8712/02/202413/02/202414/02/202415/02/202416/02/202417/02/202418/02/2024
9819/02/202420/02/202421/02/202422/02/202423/02/202424/02/202425/02/2024
10926/02/202427/02/202428/02/202429/02/20241/03/20242/03/20243/03/2024
11104/03/20245/03/20246/03/20247/03/20248/03/20249/03/202410/03/2024
121111/03/202412/03/202413/03/202414/03/202415/03/202416/03/202417/03/2024
131218/03/202419/03/202420/03/202421/03/202422/03/202423/03/202424/03/2024
141325/03/202426/03/202427/03/202428/03/202429/03/202430/03/202431/03/2024
15141/04/20242/04/20243/04/20244/04/20245/04/20246/04/20247/04/2024
16158/04/20249/04/202410/04/202411/04/202412/04/202413/04/202414/04/2024
171615/04/202416/04/202417/04/202418/04/202419/04/202420/04/202421/04/2024
181722/04/202423/04/202424/04/202425/04/202426/04/202427/04/202428/04/2024
191829/04/202430/04/20241/05/20242/05/20243/05/20244/05/20245/05/2024
20196/05/20247/05/20248/05/20249/05/202410/05/202411/05/202412/05/2024
212013/05/202414/05/202415/05/202416/05/202417/05/202418/05/202419/05/2024
222120/05/202421/05/202422/05/202423/05/202424/05/202425/05/202426/05/2024
232227/05/202428/05/202429/05/202430/05/202431/05/20241/06/20242/06/2024
24233/06/20244/06/20245/06/20246/06/20247/06/20248/06/20249/06/2024
252410/06/202411/06/202412/06/202413/06/202414/06/202415/06/202416/06/2024
262517/06/202418/06/202419/06/202420/06/202421/06/202422/06/202423/06/2024
272624/06/202425/06/202426/06/202427/06/202428/06/202429/06/202430/06/2024
28271/07/20242/07/20243/07/20244/07/20245/07/20246/07/20247/07/2024
29288/07/20249/07/202410/07/202411/07/202412/07/202413/07/202414/07/2024
302915/07/202416/07/202417/07/202418/07/202419/07/202420/07/202421/07/2024
313022/07/202423/07/202424/07/202425/07/202426/07/202427/07/202428/07/2024
323129/07/202430/07/202431/07/20241/08/20242/08/20243/08/20244/08/2024
33325/08/20246/08/20247/08/20248/08/20249/08/202410/08/202411/08/2024
343312/08/202413/08/202414/08/202415/08/202416/08/202417/08/202418/08/2024
353419/08/202420/08/202421/08/202422/08/202423/08/202424/08/202425/08/2024
363526/08/202427/08/202428/08/202429/08/202430/08/202431/08/20241/09/2024
37362/09/20243/09/20244/09/20245/09/20246/09/20247/09/20248/09/2024
38379/09/202410/09/202411/09/202412/09/202413/09/202414/09/202415/09/2024
393816/09/202417/09/202418/09/202419/09/202420/09/202421/09/202422/09/2024
403923/09/202424/09/202425/09/202426/09/202427/09/202428/09/202429/09/2024
414030/09/20241/10/20242/10/20243/10/20244/10/20245/10/20246/10/2024
42417/10/20248/10/20249/10/202410/10/202411/10/202412/10/202413/10/2024
434214/10/202415/10/202416/10/202417/10/202418/10/202419/10/202420/10/2024
444321/10/202422/10/202423/10/202424/10/202425/10/202426/10/202427/10/2024
454428/10/202429/10/202430/10/202431/10/20241/11/20242/11/20243/11/2024
46454/11/20245/11/20246/11/20247/11/20248/11/20249/11/202410/11/2024
474611/11/202412/11/202413/11/202414/11/202415/11/202416/11/202417/11/2024
484718/11/202419/11/202420/11/202421/11/202422/11/202423/11/202424/11/2024
494825/11/202426/11/202427/11/202428/11/202429/11/202430/11/20241/12/2024
50492/12/20243/12/20244/12/20245/12/20246/12/20247/12/20248/12/2024
51509/12/202410/12/202411/12/202412/12/202413/12/202414/12/202415/12/2024
525116/12/202417/12/202418/12/202419/12/202420/12/202421/12/202422/12/2024
535223/12/202424/12/202425/12/202426/12/202427/12/202428/12/202429/12/2024
545330/12/202431/12/20241/01/20252/01/20253/01/20254/01/20255/01/2025
Sheet1
Cell Formulas
RangeFormula
C2:J54C2=LET( Dte,TODAY(), YrStart, DATE(YEAR(Dte),1,1), YrEnd, EOMONTH(YrStart,12), DOW_Start, WEEKDAY(YrStart,2), DaysRange, YrEnd-YrStart+DOW_Start, DayStart, YrStart-DOW_Start+1, CalDays,SEQUENCE(INT(366/7)+1,7,DayStart,1), WeekNums,WEEKNUM(TAKE(CalDays,,1),2), HSTACK(WeekNums,CalDays))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:J54Expression=ISNUMBER(XMATCH(D2;OFFSET($C2;;IF(MOD($C2;4)=0;4;MOD($C2;4));;4)))textNO
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
15
Views
326
dmt32
D
A
  • Question Question
Copy & Paste from workbook to another with criterias
Replies
2
Views
498
Anthony86
A
D
  • Question Question
Formula to "Review" Alternating Shift Schedules
Replies
0
Views
136
davidcox9
D
F
  • Solved
How to change value from data validation based on value in col A?
Replies
5
Views
332
Eric W
Eric W
W
  • Solved
Formula help
Replies
5
Views
327
Fluff
Fluff

Forum statistics

Threads
1,223,847
Messages
6,174,991
Members
452,598
Latest member
jeffreyp

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