Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 5868
• Last Modified:

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

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
lcha
Asked:
• 2
1 Solution

Commented:
use the NETWORKDAYS function

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

Commented:
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 Commented:
OK, thanks so much.  I will try this out tomorrow morning and let you the result.
0

Author Commented:
Thank you!
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.