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
 
Sort by date Sort by votes
yes, now it makes sense... but which have priority?
And what colors do you want for all three. I thought I only saw two colors.
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
awoohaw said:
yes, now it makes sense... but which have priority?
And what colors do you want for all three. I thought I only saw two colors.
Click to expand...
So, from the received date to the start date is failed service and can be red, the start date to the end date is the service being delivered and can be grey, the received date to the RTP date is a failed service and can be red.

The priority would be the start date to the end date, service being delivered.
 
Upvote 0
ok, try this:

Mr excel questions 35.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
602023-04-01
610108152229
620102030405060708091011121314151617181920212223242526272829300102030405
63 ReceivedReceived TimeStart DateStart TimeEnd DateEnd TimeRTP RTP TimeDays until StartedSpanStart to End Days% FacilitatedSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTF
642023-03-3119:16:002023-04-0119:45:002023-04-301302997%
652023-03-2921:23:002023-03-3017:45:002023-04-2016:06:001222195%
662023-04-0215:31:002023-04-0216:30:002023-04-3002828100%
672023-04-0220:42:002023-04-0221:45:002023-04-0415:59:00022100%
682023-04-0306:11:002023-04-0413:39:00011100%
692023-04-0406:45:002023-04-0410:56:00011100%
702023-04-0401:36:002023-04-0404:00:002023-04-0716:34:00033100%
712023-04-0506:22:002023-04-0619:58:00011100%
722023-04-0504:37:002023-04-0510:28:000100%
732023-04-0503:39:002023-04-0517:47:000100%
742023-04-0503:38:002023-04-0720:15:002023-04-302252392%
752023-04-0323:09:002023-04-0411:55:002023-04-0517:55:0012150%
762023-04-0512:45:002023-04-1010:05:000500%
772023-04-0516:20:002023-04-0520:03:000100%
782023-04-0613:10:002023-04-0621:05:000100%
792023-04-0718:22:002023-04-0714:58:000100%
Steff9910
Cell Formulas
RangeFormula
N61,U61,AB61,AI61,AP61N61=N5
N62N62=M3
O62:AV62O62=N5+1
N63:AV63N63=LEFT(TEXT(N5,"ddd"),1)
J64:J79J64=IF(OR(B7="",H7<>""),0,D7-B7)
K64:K79K64=IF(B7="", 1, IF(F7="", IF(H7=B7, 1, H7-B7), IF(H7<>"", H7, F7)-B7))
L64:L79L64=IF(H7="", IF(F7=D7,1,F7-D7), 0)
M64:M79M64=IFERROR(L7/K7, 0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N64:AV79Expression=AND(N$62>=$D64,N$62<=$F64)textYES
N64:AV79Expression=AND(N$62>=$B64,N$62<=$D64)textNO
N64:AV79Expression=AND(N$62>=$B64,N$62<=$H64)textNO
 
Upvote 0
awoohaw said:
ok, try this:

Mr excel questions 35.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
602023-04-01
610108152229
620102030405060708091011121314151617181920212223242526272829300102030405
63 ReceivedReceived TimeStart DateStart TimeEnd DateEnd TimeRTP RTP TimeDays until StartedSpanStart to End Days% FacilitatedSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTF
642023-03-3119:16:002023-04-0119:45:002023-04-301302997%
652023-03-2921:23:002023-03-3017:45:002023-04-2016:06:001222195%
662023-04-0215:31:002023-04-0216:30:002023-04-3002828100%
672023-04-0220:42:002023-04-0221:45:002023-04-0415:59:00022100%
682023-04-0306:11:002023-04-0413:39:00011100%
692023-04-0406:45:002023-04-0410:56:00011100%
702023-04-0401:36:002023-04-0404:00:002023-04-0716:34:00033100%
712023-04-0506:22:002023-04-0619:58:00011100%
722023-04-0504:37:002023-04-0510:28:000100%
732023-04-0503:39:002023-04-0517:47:000100%
742023-04-0503:38:002023-04-0720:15:002023-04-302252392%
752023-04-0323:09:002023-04-0411:55:002023-04-0517:55:0012150%
762023-04-0512:45:002023-04-1010:05:000500%
772023-04-0516:20:002023-04-0520:03:000100%
782023-04-0613:10:002023-04-0621:05:000100%
792023-04-0718:22:002023-04-0714:58:000100%
Steff9910
Cell Formulas
RangeFormula
N61,U61,AB61,AI61,AP61N61=N5
N62N62=M3
O62:AV62O62=N5+1
N63:AV63N63=LEFT(TEXT(N5,"ddd"),1)
J64:J79J64=IF(OR(B7="",H7<>""),0,D7-B7)
K64:K79K64=IF(B7="", 1, IF(F7="", IF(H7=B7, 1, H7-B7), IF(H7<>"", H7, F7)-B7))
L64:L79L64=IF(H7="", IF(F7=D7,1,F7-D7), 0)
M64:M79M64=IFERROR(L7/K7, 0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N64:AV79Expression=AND(N$62>=$D64,N$62<=$F64)textYES
N64:AV79Expression=AND(N$62>=$B64,N$62<=$D64)textNO
N64:AV79Expression=AND(N$62>=$B64,N$62<=$H64)textNO
Click to expand...
awoohaw, this is fantastic, thank you, and works well as wanted it too.

Only one more question, is it possible to stop the cells highlighting in red if there is no received date like in rows 68, 69 and 71?

Once again thank you so much, I am one happy bloke.
 
Upvote 0
change that particular rule to:

Excel Formula: 
=AND(N$62>=$B64,N$62<=$D64,ISNUMBER($B64))


I'm happy you were able to find a solution here.

Best Wishes!
 
Upvote 0
Solution
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
952
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
629
jtakw
jtakw
T
  • Question Question
cycle through multiple sheets
Replies
6
Views
766
ttowncorp
T
R
  • Question Question
Help!
Replies
3
Views
2K
63falcondude
63falcondude

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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