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:
lcha
  • 2
1 Solution
 
viralypatelCommented:
use the NETWORKDAYS function


=NETWORKDAYS(K2,TODAY())

Open in new window

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

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.

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

Good luck!
McOz
0
 
lchaAuthor Commented:
OK, thanks so much.  I will try this out tomorrow morning and let you the result.  
0
 
lchaAuthor Commented:
Thank you!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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