Help with #VALUE when code is in the cell

dchaney

Well-known Member
Joined
Jun 4, 2008
Messages
732
Office Version
  1. 2016
Platform
  1. Windows
Hello All,

I am working on a spreadsheet to populate the number of days (Minus weekends) that it takes to resolve an issue. The problem I am having is that if the Open date and the Resolved date are both blank (Minus the formula that populates the cell) I get #VALUE. Below is the current code I am using

=IF($D4,NETWORKDAYS($D4,IF($E4="",TODAY(),$E4))-SIGN(NETWORKDAYS($D4,IF($E4="",TODAY(),$E4))),"")

Column D = Open Date
Column E = Resolved Date
This formula is in Column F
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Column D contains =IF('Time - Week 1'!$I3 > 1,'Time - Week 1'!$I3,"")
Column E contains =IF('Time - Week 1'!$H3 > 1,'Time - Week 1'!$H3,"")

So when =IF($D3,NETWORKDAYS($D3,IF($E3="",TODAY(),$E3))-SIGN(NETWORKDAYS($D3,IF($E3="",TODAY(),$E3))),"") looks at these cells I assume they do not appear empty so it tosses the #VALUE error. I just can not figure out how to make it show nothing unless there is an actual date in column D and E.

I am trying to automate a spreadsheet that takes me hours to update each month. My team fill in this sheet on a daily basis, and I am trying to get this data to reflect on my master sheet so I can save time.
 
Upvote 0
It's long but seems to work

=IF(ISERROR(IF($D4,NETWORKDAYS($D4,IF($E4="",TODAY(),$E4))-SIGN(NETWORKDAYS($D4,IF($E4="",TODAY(),$E4))),"")),"",IF($D4,NETWORKDAYS($D4,IF($E4="",TODAY(),$E4))-SIGN(NETWORKDAYS($D4,IF($E4="",TODAY(),$E4))),""))
 
Upvote 0
Thanks a lot for your help, works perfect for my needs :)
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top