Take 2 - Conditional Formatting to highlight cells between to date

S

Stef9910

Board Regular
Joined
Nov 2, 2022
Messages
72
Office Version
  1. 2010
Platform
  1. Windows
Hello everyone,

So yesterday I had some great help from PeteWright, but I cannot get my conditional formatting to work and have really confused myself, so, please could one of you awesome people help me with another way.

Yesterday the spreadsheet for the conditional formatting was based on a pivot chart table, it had the word (Blank) in quite a few cells where there was no date and i think this is why i have been getting really confused, so below is a mini sheet from the main table.

This table does not have the word (Blank) in it, just an empty cell, below the mini sheet is an example i have colored the cells in manually, to show the results i am after.

Would it be possible for someone to work out the formula's for me that i can input into the conditional formatting as a new rule(s)?

Any help would be greatly received.

Thank you,

Stefan

Example for conditional formatting.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
1
2
3April
41-Apr-238-Apr-2315-Apr-2322-Apr-2329-Apr-23
512345678910111213141516171819202122232425262728293012345
6 ReceivedReceived TimeStart DateStart TimeEnd DateEnd TimeRTP RTP TimeDays until StartedSpanStart to End Days% FacilitatedSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTF
731/3/2319:161/4/2319:4530/4/231302997%
829/3/2321:2330/3/2317:4520/4/2316:061222195%
92/4/2315:312/4/2316:3030/4/2302828100%
102/4/2320:422/4/2321:454/4/2315:59022100%
113/4/236:114/4/2313:39011100%
124/4/236:454/4/2310:56011100%
134/4/231:364/4/234:007/4/2316:34033100%
145/4/236:226/4/2319:58011100%
155/4/234:375/4/2310:280100%
165/4/233:395/4/2317:470100%
175/4/233:387/4/2320:1530/4/232252392%
183/4/2323:094/4/2311:555/4/2317:5512150%
195/4/2312:4510/4/2310:050500%
205/4/2316:205/4/2320:030100%
216/4/2313:106/4/2321:050100%
227/4/2318:227/4/2314:580100%
23
24
25April
261-Apr-238-Apr-2315-Apr-2322-Apr-2329-Apr-23
2712345678910111213141516171819202122232425262728293012345
28 ReceivedReceived TimeStart DateStart TimeEnd DateEnd TimeRTP RTP TimeDays until StartedSpanStart to End Days% FacilitatedSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTF
2931/3/2319:161/4/2319:4530/4/231302997%
3029/3/2321:2330/3/2317:4520/4/2316:061222195%
312/4/2315:312/4/2316:3030/4/2302828100%
322/4/2320:422/4/2321:454/4/2315:59022100%
333/4/236:114/4/2313:39011100%
344/4/236:454/4/2310:56011100%
354/4/231:364/4/234:007/4/2316:34033100%
365/4/236:226/4/2319:58011100%
375/4/234:375/4/2310:280100%
38
Sheet1
Cell Formulas
RangeFormula
N4,U4,AB4,AI4,AP4,N26,U26,AB26,AI26,AP26N4=N5
N5,N27N5=M3
O5:AV5,O27:AV27O5=N5+1
N6:AV6,N28:AV28N6=LEFT(TEXT(N5,"ddd"),1)
J7:J22,J29:J37J7=IF(OR(B7="",H7<>""),0,D7-B7)
K7:K22,K29:K37K7=IF(B7="", 1, IF(F7="", IF(H7=B7, 1, H7-B7), IF(H7<>"", H7, F7)-B7))
L7:L22,L29:L37L7=IF(H7="", IF(F7=D7,1,F7-D7), 0)
M7:M22,M29:M37M7=IFERROR(L7/K7, 0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M29:M37Other TypeDataBarNO
M7:M22Other TypeDataBarNO
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
what are the rules that you used to manually color the cells?
 
Upvote 0
awoohaw said:
what are the rules that you used to manually color the cells?
Click to expand...
I just colored them to illustrate what I would like the cells to look like
awoohaw said:
what are the rules that you used to manually color the cells?
Click to expand...
The new rule formula used as a basis to start with from the help given to me last night is = IF( AND( J$3>=$F5,J$3<=$G5), 1, "").

This was from a different workbook so the cell ranges will be different, but the formula will be in the same format, if that makes sense.
 
Upvote 0
i'm sorry. but you need to provide some better specifics. Using the mini worksheet in your post J3, F3, F5, and G5 all have no data. How can a formula provide results if there are no inputs?
 
Upvote 0
awoohaw said:
i'm sorry. but you need to provide some better specifics. Using the mini worksheet in your post J3, F3, F5, and G5 all have no data. How can a formula provide results if there are no inputs?
Click to expand...
Thanks for your reply, the below mini sheet has a the new rule added to it, i have no idea where I am going wrong, like i said, i've confused my self.

Hope to below mini sheet helps?

Example for conditional formatting.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
3April
41-Apr-238-Apr-2315-Apr-2322-Apr-2329-Apr-23
512345678910111213141516171819202122232425262728293012345
6 ReceivedReceived TimeStart DateStart TimeEnd DateEnd TimeRTP RTP TimeDays until StartedSpanStart to End Days% FacilitatedSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTF
731/3/2319:161/4/2319:4530/4/231302997%
829/3/2321:2330/3/2317:4520/4/2316:061222195%
92/4/2315:312/4/2316:3030/4/2302828100%
102/4/2320:422/4/2321:454/4/2315:59022100%
113/4/236:114/4/2313:39011100%
124/4/236:454/4/2310:56011100%
134/4/231:364/4/234:007/4/2316:34033100%
145/4/236:226/4/2319:58011100%
155/4/234:375/4/2310:280100%
165/4/233:395/4/2317:470100%
175/4/233:387/4/2320:1530/4/232252392%
183/4/2323:094/4/2311:555/4/2317:5512150%
195/4/2312:4510/4/2310:050500%
205/4/2316:205/4/2320:030100%
216/4/2313:106/4/2321:050100%
227/4/2318:227/4/2314:580100%
23
Sheet1
Cell Formulas
RangeFormula
N4,U4,AB4,AI4,AP4N4=N5
N5N5=M3
O5:AV5O5=N5+1
N6:AV6N6=LEFT(TEXT(N5,"ddd"),1)
J7:J22J7=IF(OR(B7="",H7<>""),0,D7-B7)
K7:K22K7=IF(B7="", 1, IF(F7="", IF(H7=B7, 1, H7-B7), IF(H7<>"", H7, F7)-B7))
L7:L22L7=IF(H7="", IF(F7=D7,1,F7-D7), 0)
M7:M22M7=IFERROR(L7/K7, 0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N7:AV100Expression=AND(N$5>=$B7,N$5<=$D7)textNO
M7:M22Other TypeDataBarNO
 
Upvote 0
the mini sheet you gave helps, but it doesn't have the gray cells your first post has.
Here is my take on your formulas, based upon the sample data in your first post. The formla results are just my calculations for hte conditional formatting, and would be taken out in the final worksheet.

Book2
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
422023-04-01
432023-04-012023-04-082023-04-152023-04-222023-04-29
440102030405060708091011121314151617181920212223242526272829300102030405
45 ReceivedReceived TimeStart DateStart TimeEnd DateEnd TimeRTP RTP TimeDays until StartedSpanStart to End Days% FacilitatedSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTF
462023-03-3119:16:002023-04-0119:45:002023-04-301302997%10000000000000000000000000000000000
472023-03-2921:23:002023-03-3017:45:002023-04-2016:06:001222195%00000000000000000000000000000000000
482023-04-0215:31:002023-04-0216:30:002023-04-3002828100%01000000000000000000000000000000000
492023-04-0220:42:002023-04-0221:45:002023-04-0415:59:00022100%01000000000000000000000000000000000
502023-04-0306:11:002023-04-0413:39:00011100%11100000000000000000000000000000000
512023-04-0406:45:002023-04-0410:56:00011100%11110000000000000000000000000000000
522023-04-0401:36:002023-04-0404:00:002023-04-0716:34:00033100%00010000000000000000000000000000000
532023-04-0506:22:002023-04-0619:58:00011100%11111000000000000000000000000000000
542023-04-0504:37:002023-04-0510:28:000100%00000000000000000000000000000000000
Steff9910
Cell Formulas
RangeFormula
N43,AP43,AI43,AB43,U43N43=N44
N44N44=M42
O44:AV44O44=N44+1
N45:AV45N45=LEFT(TEXT(N44,"ddd"),1)
J46:J54J46=IF(OR(B46="",H46<>""),0,D46-B46)
K46:K54K46=IF(B46="", 1, IF(F46="", IF(H46=B46, 1, H46-B46), IF(H46<>"", H46, F46)-B46))
L46:L54L46=IF(H46="", IF(F46=D46,1,F46-D46), 0)
M46:M54M46=IFERROR(L46/K46, 0)
N46:AV54N46=--(AND(N$44>=$B46,N$44<=$D46))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O46:AV54Expression=--(AND(O$44>=$B46,O$44<=$D46))textNO
N46:N54Expression=--(AND(N$44>=$B46,N$44<=$D46))textNO
 
Upvote 0
awoohaw said:
did the conditional formatting formulas work?
Click to expand...
No, sorry they did not work, the grey colored cells were filled manually, therefore no formula, i colored them for illustration purposes only to show the desired result, i really want this to work, but think i might just give up, thank you so much for your time awoohaw, it really is appreciated.
 
Upvote 0
Stef9910 said:
No, sorry they did not work, the grey colored cells were filled manually, therefore no formula, i colored them for illustration purposes only to show the desired result, i really want this to work, but think i might just give up, thank you so much for your time awoohaw, it really is appreciated.
Click to expand...
I might not have been clear when asking for help, i would like the received date to the start date highlighted, the start date to the end date highlighted and the received date to the RTP date highlighted, so three conditions, if that makes sense.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Conditional Formatting to highlight cells between to date
2
Replies
12
Views
937
PeteWright
PeteWright
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
bradjsteve88
  • Question Question
Conditionally format worksheet text from multiple cells containing text.
Replies
1
Views
624
jtakw
jtakw
T
  • Question Question
cycle through multiple sheets
Replies
6
Views
763
ttowncorp
T
R
  • Question Question
Help!
Replies
3
Views
2K
63falcondude
63falcondude

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

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