using networkdays with blank cells

sooroo

New Member
Joined
Dec 6, 2005
Messages
27
I am trying to get some help regarding a report I'm trying to create. There are many columns of dates - basically tracking at what stage the different projects are at. Some projects only have a few dates filled in, and some have the majority filled in.

I'm trying to find the cycle times of each phase of the project, but sometimes there are empty cells because the phase/project has not been completed yet. I am trying to find a way to replace an empty cell with today's date so that I can figure out the current cycle time of the entire project, as well as each phase.

I thought that I could do a formula that tested if the cell is empty, and if the cell was empty, I could replace it with today's date. However, I have 2 problems - 1. I don't know how to test if a cell is empty when writing a formula. The only approach I could find was if(X2=" ") for non blank cells. 2. I am a novice to networkdays, so I don't know if this formula is being overtaxed.

One of my meager attempts was to try:
=IF(X2=" ","NA",NETWORKDAYS(X2,IF(Y2=" ",Y2,TODAY()),'Holiday Dates'!$A$1:$A$9))

X2=Date In
Y2=Date Out

Thanks to anyone for their insights :rolleyes:
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

sooroo

New Member
Joined
Dec 6, 2005
Messages
27
Amazingly everything works now! Thanks so much!

I'm not worthy! :pray:
 

sooroo

New Member
Joined
Dec 6, 2005
Messages
27
Uh Oh

I spoke too soon. Everything is still fine with this solution, but now I need to find out the code for non blank cells.

If X="" means a blank cell, does X="*" mean a nonblank cell?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

In what context?

you could try

x<>""
 

sooroo

New Member
Joined
Dec 6, 2005
Messages
27
For the If then statement I was trying to formulate earlier.

=IF(H2="","NA",IF(I2="",NETWORKDAYS(H2,TODAY(),'Holiday Dates'!$A$1:$A$9)-1,"Done"))

If I wanted to substitute IF(H2="") blank cell for a non blank cell, what would be the correct syntax?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

as suggested above..

=IF(H2<>""........

<> means "does not equal"
 

sooroo

New Member
Joined
Dec 6, 2005
Messages
27
Thanks

Thanks so much for the info. Everything is working now.

Is there a place in Microsoft help or somewhere else that I can get all those operators so I can reference in the future? I looked all over Microsoft's website and help function and I couldn't find anything.... it's possible that I was phrasing my search incorrectly, though...
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
You don't indicate which version of Excel you're using, but at least for XL2003, if you just open help from the menu (don't use the search box) and expand the options, the chain is:<ul>[*]Table of Contents<ul>[*]Working with Data<ul>[*]Formulas<ul>[*]Creating Formulas<ul>[*]About Calculation Operators[/list][/list][/list][/list][/list]HTH
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The way I was taught a long time ago to remember that this <> is not equal to, is that technically it means less than or greater than, because you can combine comparison operators (i.e. >= greater than or equal to, <= less than or equal to).

Thinking about this using numbers, if it is greater than or less than a number, it can't be equal to it (not equal to).

Hope that helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,226
Messages
5,570,990
Members
412,353
Latest member
SofiaV
Top