Colouring a cell 42 days from a date

[Caulkie]

Hi

I've spent all day trying to work out how to do this and have failed.

I've been given the task by my manager to highlight a cell per vehicle in a Fleet Service Schedule. Each vehicle is serviced every sixth Monday (every 42 days).


Cell B7 is 01/01/2018 and B8 is 02/01/2018 recurring.........
Cell C7 is irrelevant (just showing the day, in this case Monday)
Cell D7 is for the first Vehicle.

I have a second sheet (Table) with all the service dates for 2018 for the vehicle in D7.

So in D7 I've put an IF statement.......... =IF(B7 = "'Base copy'!A2:A10", "green", "white")

In the table, column A has the 10 dates through the year , so with B7 being 01/01/2018 and A2 of Table also being 01/01/2018 I would expect to see the word Green.

Maybe I'm using the wrong formula, but the end result is to have the cells of the service dates of column D to be filled Green (at the moment I'm just using the word Green).

Please help

 

[barry houdini]

Re: Help: Colouring a cell 42 days from a date

Hello Caulkie, welcome to MrExcel

For a worksheet formula try using COUNTIF like this

=IF(COUNTIF(‘Base copy'!$A$2:$A$10,B7)>0, "green", "white")

For conditional formatting just use the “test” on its own, I.e.

=COUNTIF(‘Base copy'!$A$2:$A$10,B7)>0

 

[steve the fish]

Re: Help: Colouring a cell 42 days from a date

Maybe try this conditional formatting formula:

=ISNUMBER(MATCH(B7,'base copy'!$A$1:$A$10,0))

 

[Caulkie]

Re: Help: Colouring a cell 42 days from a date

Gentlemen, thank you from the bottom of my heart, I'll try these right now, thank you again

 

[Caulkie]

Re: Help: Colouring a cell 42 days from a date

Hello Caulkie, welcome to MrExcel

For a worksheet formula try using COUNTIF like this

=IF(COUNTIF(‘Base copy'!$A$2:$A$10,B7)>0, "green", "white")

For conditional formatting just use the “test” on its own, I.e.

=COUNTIF(‘Base copy'!$A$2:$A$10,B7)>0

Thanks for the welcome Barry

That formula worked a treat, but I now have to fill the cell with the colour green instead of the word "Green" (TRUE) and leave the cell blank if it is "White" (FALSE). Would you have any idea how to change the formula so that it will fill the cell with green please?

 

[Scott T]

Re: Help: Colouring a cell 42 days from a date

Thanks for the welcome Barry

That formula worked a treat, but I now have to fill the cell with the colour green instead of the word "Green" (TRUE) and leave the cell blank if it is "White" (FALSE). Would you have any idea how to change the formula so that it will fill the cell with green please?

You need to use Conditional Formatting


Select the cell or cells you want to format
In Conditional formatting select new rule.
Select Use a formula to determine which cells to format
use the formula Barry posted
=COUNTIF(‘Base copy'!$A$2:$A$10,B7)>0

Select your formatting by clicking on Format
Click OK

 

[Caulkie]

Re: Help: Colouring a cell 42 days from a date

You need to use Conditional Formatting


Select the cell or cells you want to format
In Conditional formatting select new rule.
Select Use a formula to determine which cells to format
use the formula Barry posted
=COUNTIF(‘Base copy'!$A$2:$A$10,B7)>0

Select your formatting by clicking on Format
Click OK

Thank you, that worked perfectly
I didn't think that way, you've broadened my way of thinking