+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    11-11-2021
    Location
    New Zealand
    MS-Off Ver
    Office 365
    Posts
    13

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    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
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    11-11-2021
    Location
    New Zealand
    MS-Off Ver
    Office 365
    Posts
    13

    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
    Last edited by docGee; 04-14-2022 at 04:58 PM. Reason: correcting invalid attachamnet

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    11-11-2021
    Location
    New Zealand
    MS-Off Ver
    Office 365
    Posts
    13

    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. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-11-2021
    Location
    New Zealand
    MS-Off Ver
    Office 365
    Posts
    13

    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.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    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. #9
    Registered User
    Join Date
    11-11-2021
    Location
    New Zealand
    MS-Off Ver
    Office 365
    Posts
    13

    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. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    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. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Format based on column values
    By Barking_Mad in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-10-2020, 08:59 AM
  2. Conditional format based on another cell column carried down column?
    By ROsmond in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-27-2020, 01:47 PM
  3. [SOLVED] Sum values in Column A, based on dates in Column B & if value in Column C = True
    By rtcwlomax in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-25-2016, 12:03 PM
  4. [SOLVED] Format cell based on column of dates
    By klynne75 in forum Excel General
    Replies: 4
    Last Post: 03-23-2013, 12:55 AM
  5. Format cell based on column of dates
    By klynne75 in forum Excel General
    Replies: 1
    Last Post: 03-23-2013, 12:54 AM
  6. [SOLVED] Insert Rows, based on values in a column, for a 300,000 row dataset
    By engineerlady in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-04-2013, 04:47 PM
  7. [SOLVED] conditional format row based on cell in column A
    By ea223 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-22-2013, 11:36 AM

Tags for this Thread

Bookmarks

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