+ Reply to Thread
Results 1 to 4 of 4

Formatting

  1. #1
    Registered User
    Join Date
    03-25-2011
    Location
    Oakville, Canada
    MS-Off Ver
    Excel 2003
    Posts
    12

    Formatting

    Assume the first cell address in the top left hand corner is cell A1. Cells A1:C8 are all dates, formatted differently (columns 1&2 are seperate of column 3). I need to format cell C to be flagged with red font or a red background when the date in cell C is less then 2 weeks away from the date in columns 1 or 2. This has to be coupled with the condition that column K is "Yes". Is this possible?

    If not, is it possible to shift all cells right, and create a blank column A and enter a formula here where this cell will then be flagged if it meets above conditions?

    I.e. Take a look at Row 4. 32411 means March 24th, 2011. Being that 1-Apr-11 in column C is less then 2 weeks away from March 24th, 2011 and K4 is flagged "Yes", is it possible to enter a condition that will flag this?! Hopefully this makes sense. Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,454

    Re: Formatting Question

    Yes, it is possible.

    Something like:

    =AND(C1<>"",MAX(A1:B1)>C1-14,k1="Yes")

    used as the formula for Conditional Formatting.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    14,936

    Re: Formatting Question

    Hi turnpink and welcome to the forum.

    Find the attached with your answer in two ways.
    1. In Col D is True or False if it meets the conditions above.
    2. Conditional Formatting of font in col C is red if it meeds above.

    Press F9 to generate different dates and check if it does what you want.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    03-25-2011
    Location
    Oakville, Canada
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Formatting

    Thanks for your response... I decided to conditionally format column C as you advised above... The problem i see with this, is that the formula is ignoring the month and rather only considering the day of the month. For instance if column k is "yes" and cells A & B are 32711 (March 27, 2011), while column C is 25-April-2011.. column C would format to red... Clearly, these dates aren't within 14 days of each other, however the (a-c)>14 seems to only consider the days (27-25)... Any thoughts? Again, any help is greatly appreciated. Please see attched for example.
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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