Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Conditional Formatting (Excel 2003)

    Hi Excel Experts,

    I would like to track the time taken to process invoices so as to followup on the same.

    I need a formula for highlighting the rows which are overdue 5 days from today. i.e. any invoices are pending for more than 5 days from today ONE COLOUR . And if the date is in the last month then ANOTHER COLOUR.

    Thanks in advance

    Regards
    Baiju

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Excel 2003)

    > if the date is in the last month then ANOTHER COLOUR.

    Do you mean dates in the last calendar month, or dates more than a month overdue?

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2003)

    Select the cell and then Conditional Formatting from the Format menu. Select "Cell value is" in the first drop down list, and "less than" in the second. Enter this formula in the formula box:

    <code>
    =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))
    </code>

    Click the Format button and select the format you want for over a month.

    Click the Add button and fill the two drop down boxes the same as above. In the formula box enter:

    <code>
    =TODAY()-5
    </code>

    Click the Format button and select the formatting you want for over 5 days.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2003)

    Hi Hans,

    Dates more than 5 days over due and more than a month overdue

    Regards
    Baiju

  5. #5
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2003)

    Hi Legare,

    Thanks for the formula, it works perfectly for the cell which has the date, however if i have to change the colour for the entire row, then i need to you the Formula is in the Conditional formatting.

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Excel 2003)

    See attached workbook.
    Conditional formatting has been set for rows 2 through 13.
    When you select rows 2 through 13, set the formula for conditional formatting as it should be for row 2; Excel will automatically adjust it for the other rows.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2003)

    Hi Hans,

    Thanks for the formula as per the attachment its working great. I need to make a small enhancement to the same.
    in A2 based on the date it changes colour. However, in B2 if i enter DONE the formatting should go. i.e. if B2<> "" then remove formatting.

    Can you help me on this.

    Regards
    Baiju

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Excel 2003)

    Change the first formula to
    <code>
    =AND($B2="",$A2<DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))
    </code>
    and the second one to
    <code>
    =AND($B2="",$A2<TODAY()-5)</code>

  9. #9
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2003)

    Thanks Hans,

    Its working great.

    Regards
    Baiju

Posting Permissions

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