# Conditional format a cell based on values in a dataset column and dates in another column

1. ## Conditional format a cell based on values in a dataset column and dates in another column

Hi
Attached is an example xl sheet where i am struggling a bit with the conditional formatting of cells. The sheet is simplified for this question. The following is the scenario
Sheet 1: is a summary sheet which gives the number of occurrences of data month-wise. Data is in the second sheet.
Sheet 2: contains dates in one column and Pass/Fail in the second column. Dates in the first column are refreshed as they are obtained from the database on a sql query. The Pass/Fail column is a formula column which gets calculated based on data in other columns.

I could successfully get the counts for occurrences of data in a particular month on the Summary sheet. - e.g. 2 for Jan, 3 for Feb and so on.

What i am struggling with is to find a conditional format formula under the following scenarios:
1. Check the whole dataset in column 1 to find the month and if its Jan e.g. row 2 and 3 then check the corresponding values in the 2nd column.
• If all are PASS for that month then format the month cell (B2) in Summary sheet to green
• If all are FAIL for that month then format the month cell (B2) in Summary sheet to red
• If some are PASS and some FAIL for that month then format the month cell (B2) in Summary sheet to orange

What should the conditional format formulae be to achieve the above. Both the dates and the Pass/Fail columns have named ranges - mthRange, PFRange.

Any help is appreciated.
docGee

2. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

Rule for green:

=COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022")-COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022",PFRange,"PASS")=0

Rule for red:

=COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022")-COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022",PFRange,"FAIL")=0

Rule for amber:

=COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022")-COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022",PFRange,"PASS")<>0

3. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

Hi Ali
That worked very well. Thank you. The only issue is that for some reason the formula does not recognise the date in a dataset returned by PowerQuery as date. I know this is a separate issue. My query has select convert(varchar, DATE, 3) so i would expect it to return date as General when i click Format Cell.
Please see attached picture. The date in the green cell (returned from PowerQuery) is not recognised as a "Date' while the one in white is.
Attachment 776389date.PNG

4. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

Selecting attachment 776389 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
It may be better to utilize the information in the banner at the top of the page to upload an Excel file as was done in post #1.
Let us know if you have any questions.

5. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

Hi JeteMc
I have re uploaded the image.

6. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

I can't replicate the issue.
In the attached file the data in columns A:B is converted to an Excel table and then used to produce the Power Query table in columns D:E.
The formulas on the summary sheet and the conditional formatting applied to cell B2 on that sheet recognize the dates in the Power Query table.
Perhaps uploading a file that illustrates the issue would help.
Let us know if you have any questions.

7. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

I have updated the attached sheet with an example in Data2 sheet. Please note that the calculation for April in Summary sheet B11 is not correct.

8. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

It appears that the table on the Data2 sheet is an Excel table and not produced using power query.
The data in cell J2 is text. In order to convert to a date:
1. Select cell J2
2. From the Data tab on the ribbon at the top select Text to Columns
3. Select Next and Next
4. Select Date and then change the drop down to DMY
5. Select Finish
Cell B11 on the Summary sheet should now display 1
Note that in step 1 you could select J2:J1000, however you can only select one column at a time so you will need to repeat steps 1:5 with K2:K1000 ... N2:N1000
Let us know if you have any questions.

9. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

I did the following its now working. In my SQl query (Power query) I coded to get convert date as a date. That worked. Thanks for all the help rendered. Some of your suggestions were very useful .
Regards
docGee

10. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

11. ## Re: Conditional format a cell based on values in a dataset column and dates in another col

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1