+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting between columns

  1. #1
    Registered User
    Join Date
    06-23-2022
    Location
    Boston, MA
    MS-Off Ver
    10
    Posts
    3

    Conditional formatting between columns

    I have column I the last time the patient was seen. Column J as the date when a quarterly payment is due from a patient’s insurance. Column K is the date the patient was most recently seen.

    With this particular insurance, we can only bill every 90 days and the patient has to have had to been actually seen sometime within 90 days from the LAST time they were in.

    So what I want:
    If in column I, I put 3/1/2022 as the last time they were in.

    Column J I would put 6/1/22 because that’s technically the soonest we COULD bill.

    I am going to type in Column K when the patient was seen and it would be a date between 3/1/22 and 6/1/22. I would like if I type in column K a date that is eligible, such as 4/15/22, column K to turn green, using conditional formatting. But I don’t want data that ISN’T eligible to turn green, like if I type 1/1/22, since it is not in between 3/1 and 6/1. I thought the "IF" formulas would be a good place to start but can't figure it out.

    So IF the date I enter in column K is in between the dates listed on columns I and J, then K column turns green.

    Attached is the spreadsheet.

    Much obliged!
    Attached Files Attached Files
    Last edited by phoenix611; 06-23-2022 at 03:38 PM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Conditional formatting between columns

    Hi,
    you might want to upload the actual excel file in order to help others help you.
    see the yellow banner at the top of the page for more instructions.

  3. #3
    Registered User
    Join Date
    06-23-2022
    Location
    Boston, MA
    MS-Off Ver
    10
    Posts
    3

    Re: Conditional formatting between columns

    Done. Thank you for the recommendation, was on my phone when I originally posted.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Conditional formatting between columns

    Hi,
    maybe this:

    in J:
    =EOMONTH(I2+90,0)+1

    Conditional format:
    =AND($K1>=$I1,$K1<=$J1,$I1<>"")
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-23-2022
    Location
    Boston, MA
    MS-Off Ver
    10
    Posts
    3
    Hi,

    So the top part put in J in the formula bar at the bottom part with the conditional formatting out where? K?

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Conditional formatting between columns

    conditional formatting (yoiu can also look up in google):

    Conditional Formatting (on the Home tab) > New Rule> Use a formula to determine…> Enter the above formula in Edit Rule Description window> Choose the Format Fill to preview and press OK

+ 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. Replies: 5
    Last Post: 03-13-2019, 08:04 PM
  2. [SOLVED] Conditional Formatting Between Two Columns
    By chergian in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-29-2017, 02:37 AM
  3. Conditional Formatting on two columns
    By Mr_Phil in forum Excel General
    Replies: 5
    Last Post: 09-04-2016, 03:10 AM
  4. Conditional Formatting two columns
    By May10 in forum Excel General
    Replies: 1
    Last Post: 01-09-2014, 09:00 PM
  5. Conditional formatting two columns
    By Bupitz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-13-2013, 03:54 PM
  6. conditional formatting for 3 columns (?)
    By eighcoop in forum Excel General
    Replies: 3
    Last Post: 01-06-2010, 08:02 PM
  7. Conditional Formatting 4 Columns
    By David in forum Excel General
    Replies: 14
    Last Post: 04-12-2006, 05:45 AM

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