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
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 | ||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | |||
1 | ||||||||||||||||||||||||||||||||||||||||||||||||||
2 | ||||||||||||||||||||||||||||||||||||||||||||||||||
3 | April | |||||||||||||||||||||||||||||||||||||||||||||||||
4 | 1-Apr-23 | 8-Apr-23 | 15-Apr-23 | 22-Apr-23 | 29-Apr-23 | |||||||||||||||||||||||||||||||||||||||||||||
5 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 1 | 2 | 3 | 4 | 5 | |||||||||||||||
6 | Received | Received Time | Start Date | Start Time | End Date | End Time | RTP | RTP Time | Days until Started | Span | Start to End Days | % Facilitated | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | |||
7 | 31/3/23 | 19:16 | 1/4/23 | 19:45 | 30/4/23 | 1 | 30 | 29 | 97% | |||||||||||||||||||||||||||||||||||||||||
8 | 29/3/23 | 21:23 | 30/3/23 | 17:45 | 20/4/23 | 16:06 | 1 | 22 | 21 | 95% | ||||||||||||||||||||||||||||||||||||||||
9 | 2/4/23 | 15:31 | 2/4/23 | 16:30 | 30/4/23 | 0 | 28 | 28 | 100% | |||||||||||||||||||||||||||||||||||||||||
10 | 2/4/23 | 20:42 | 2/4/23 | 21:45 | 4/4/23 | 15:59 | 0 | 2 | 2 | 100% | ||||||||||||||||||||||||||||||||||||||||
11 | 3/4/23 | 6:11 | 4/4/23 | 13:39 | 0 | 1 | 1 | 100% | ||||||||||||||||||||||||||||||||||||||||||
12 | 4/4/23 | 6:45 | 4/4/23 | 10:56 | 0 | 1 | 1 | 100% | ||||||||||||||||||||||||||||||||||||||||||
13 | 4/4/23 | 1:36 | 4/4/23 | 4:00 | 7/4/23 | 16:34 | 0 | 3 | 3 | 100% | ||||||||||||||||||||||||||||||||||||||||
14 | 5/4/23 | 6:22 | 6/4/23 | 19:58 | 0 | 1 | 1 | 100% | ||||||||||||||||||||||||||||||||||||||||||
15 | 5/4/23 | 4:37 | 5/4/23 | 10:28 | 0 | 1 | 0 | 0% | ||||||||||||||||||||||||||||||||||||||||||
16 | 5/4/23 | 3:39 | 5/4/23 | 17:47 | 0 | 1 | 0 | 0% | ||||||||||||||||||||||||||||||||||||||||||
17 | 5/4/23 | 3:38 | 7/4/23 | 20:15 | 30/4/23 | 2 | 25 | 23 | 92% | |||||||||||||||||||||||||||||||||||||||||
18 | 3/4/23 | 23:09 | 4/4/23 | 11:55 | 5/4/23 | 17:55 | 1 | 2 | 1 | 50% | ||||||||||||||||||||||||||||||||||||||||
19 | 5/4/23 | 12:45 | 10/4/23 | 10:05 | 0 | 5 | 0 | 0% | ||||||||||||||||||||||||||||||||||||||||||
20 | 5/4/23 | 16:20 | 5/4/23 | 20:03 | 0 | 1 | 0 | 0% | ||||||||||||||||||||||||||||||||||||||||||
21 | 6/4/23 | 13:10 | 6/4/23 | 21:05 | 0 | 1 | 0 | 0% | ||||||||||||||||||||||||||||||||||||||||||
22 | 7/4/23 | 18:22 | 7/4/23 | 14:58 | 0 | 1 | 0 | 0% | ||||||||||||||||||||||||||||||||||||||||||
23 | ||||||||||||||||||||||||||||||||||||||||||||||||||
24 | ||||||||||||||||||||||||||||||||||||||||||||||||||
25 | April | |||||||||||||||||||||||||||||||||||||||||||||||||
26 | 1-Apr-23 | 8-Apr-23 | 15-Apr-23 | 22-Apr-23 | 29-Apr-23 | |||||||||||||||||||||||||||||||||||||||||||||
27 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 1 | 2 | 3 | 4 | 5 | |||||||||||||||
28 | Received | Received Time | Start Date | Start Time | End Date | End Time | RTP | RTP Time | Days until Started | Span | Start to End Days | % Facilitated | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | |||
29 | 31/3/23 | 19:16 | 1/4/23 | 19:45 | 30/4/23 | 1 | 30 | 29 | 97% | |||||||||||||||||||||||||||||||||||||||||
30 | 29/3/23 | 21:23 | 30/3/23 | 17:45 | 20/4/23 | 16:06 | 1 | 22 | 21 | 95% | ||||||||||||||||||||||||||||||||||||||||
31 | 2/4/23 | 15:31 | 2/4/23 | 16:30 | 30/4/23 | 0 | 28 | 28 | 100% | |||||||||||||||||||||||||||||||||||||||||
32 | 2/4/23 | 20:42 | 2/4/23 | 21:45 | 4/4/23 | 15:59 | 0 | 2 | 2 | 100% | ||||||||||||||||||||||||||||||||||||||||
33 | 3/4/23 | 6:11 | 4/4/23 | 13:39 | 0 | 1 | 1 | 100% | ||||||||||||||||||||||||||||||||||||||||||
34 | 4/4/23 | 6:45 | 4/4/23 | 10:56 | 0 | 1 | 1 | 100% | ||||||||||||||||||||||||||||||||||||||||||
35 | 4/4/23 | 1:36 | 4/4/23 | 4:00 | 7/4/23 | 16:34 | 0 | 3 | 3 | 100% | ||||||||||||||||||||||||||||||||||||||||
36 | 5/4/23 | 6:22 | 6/4/23 | 19:58 | 0 | 1 | 1 | 100% | ||||||||||||||||||||||||||||||||||||||||||
37 | 5/4/23 | 4:37 | 5/4/23 | 10:28 | 0 | 1 | 0 | 0% | ||||||||||||||||||||||||||||||||||||||||||
38 | ||||||||||||||||||||||||||||||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N4,U4,AB4,AI4,AP4,N26,U26,AB26,AI26,AP26 | N4 | =N5 |
N5,N27 | N5 | =M3 |
O5:AV5,O27:AV27 | O5 | =N5+1 |
N6:AV6,N28:AV28 | N6 | =LEFT(TEXT(N5,"ddd"),1) |
J7:J22,J29:J37 | J7 | =IF(OR(B7="",H7<>""),0,D7-B7) |
K7:K22,K29:K37 | K7 | =IF(B7="", 1, IF(F7="", IF(H7=B7, 1, H7-B7), IF(H7<>"", H7, F7)-B7)) |
L7:L22,L29:L37 | L7 | =IF(H7="", IF(F7=D7,1,F7-D7), 0) |
M7:M22,M29:M37 | M7 | =IFERROR(L7/K7, 0) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
M29:M37 | Other Type | DataBar | NO | |
M7:M22 | Other Type | DataBar | NO |