Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    14

    Unanswered: Generating 30 60 90 Day aging reports in xl

    Hello Everyone,

    I need to create a incident management and resolution report in excel, where i need to enter a date incident/event was reported..and date it was resolved....The reports need to show incidents solved in 0-30 days, 31-60 and 61-90 day intervals. Any suggestions is greatly appreciated.

    Thanks!

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Do you have the basic data worksheet set up already? Can you attach a sample? What you ask is not difficult, but want to make sure you provide enough so that it doesn't have to be re-worked 9anymore than necessary ).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Aug 2006
    Posts
    14
    Yes, Im not sure if I could post it here. Can you provide your personal email ID, that way I could send it as an attachment.

    Thanks for your willingness to Help!

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    How do you want the interval to show? Colored cells something like this?

    Green: 0-30 Days
    Yellow: 31-60 Days
    Red: 61-90 Days
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, here is one way, using Conditional Formatting, assuming you only want to highlight the entries.

    I put the following headings in:

    B2: Incident
    C2: Open Date
    D2: Resolve Date

    Then starting in row 3, I added values.

    Select the entire region (in the example B3: D7), then go to Format > Conditional Formatting.

    In the dialog box on the left, from the dropdown change "Cell Value is" to "Formula is". Then in the box to the right, put this formula (be sure to get the dollar signs correct):

    =$D3-$C3<=30

    and click on the Format button on right, and choose green fill.

    Then click on the ADD button, and do the same for the second condition, except put this formula in the box:

    =AND($D3-$C3>30,$D3-$C3<=60)

    and click Format button,a nd choose yellow fill

    Then click the ADD button again, and in the dialog put this formula

    =AND($D3-$C3>60,$D3-$C3<=90)

    click the Format button, and choose red fill.

    Change some of the dates and see what happens to the color.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Aug 2006
    Posts
    14
    Thanks so much!

Posting Permissions

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