Solved

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

Posted on 2011-10-02
3,395 Views
Hello

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!

LCha

0
Question by:lcha

LVL 12

Expert Comment

use the NETWORKDAYS function

``````=NETWORKDAYS(K2,TODAY())
``````
0

LVL 9

Accepted Solution

Try this formula:
``````=NETWORKDAYS(B2,IF(K2>0,K2,TODAY()))
``````

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

http://office.microsoft.com/en-us/excel-help/networkdays-HP005209190.aspx

Good luck!
McOz
0

Author Comment

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

Author Closing Comment

Thank you!
0

## Featured Post

### Suggested Solutions

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.