excel - create a formula in cell to calculate the aging of

Posted on 2011-10-02
Last Modified: 2012-05-12

I'd need some help to do create a formula in a cell to do the following:

If date resolved cell (column K - e.g. K2) has no value, take today's date minus (Date reported  - column B e.g. B2).    If date resolved (K2) is has a value, then (K2  - Today's date)

In other words, I'm trying do do here is calculate the aging of a software defect/bug   If the defect hasn't been resolved yet (there will be no value in column K - date resolvedl), then the cell value (based on formula I want to create) will change on a daily basis - whatever todays date is minus the date it was reported.   Once it's resolved, it just will be the date the bug was reported minus the date it was resolved.

The calculation should not no include Sat/Sun as part of the calculation.   Please let me know if you have any questions or need clarification.   Thanks!


Question by:lcha
    LVL 12

    Expert Comment

    use the NETWORKDAYS function


    Open in new window

    LVL 9

    Accepted Solution

    Try this formula:

    Open in new window

    Here is an explanation on the NETWORKDAYS function. It also allows you to specify holidays that you want to exclude from the calculation.

    Good luck!

    Author Comment

    OK, thanks so much.  I will try this out tomorrow morning and let you the result.  

    Author Closing Comment

    Thank you!

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Outlook Free & Paid Tools
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now