How to ignore blank cells in formula counting days between two dates

[Limbo1964]

Hi

I have the following formula which counts number of days between dates in two adjacent cells excluding holidays

=NETWORKDAYS(U311,V311,$AA$4:$AA$14)-1

The $AA$4:$AA$14 refers tote range where the excluded holiday dates are stored.

I have included the -1 at the end as it over counts by one day every time. i.e. if the same date is in both cells it counts 1

However if no date is in both fields it count -1 (minus one)

If a date is just in one field it counts a very high number, example below.

[TABLE="width: 228"]
<colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;" span="2"> <col width="125" style="width: 94pt; mso-width-source: userset; mso-width-alt: 4571;"> <tbody>[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 89, bgcolor: transparent"] [/TD]
[TD="width: 125, bgcolor: #D8E4BC"]-1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]16/07/2014
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #D8E4BC"]-29879[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20/06/2014
[/TD]
[TD="bgcolor: transparent"]08/07/2014[/TD]
[TD="bgcolor: #D8E4BC"]12[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04/07/2014
[/TD]
[TD="bgcolor: transparent"]16/07/2014[/TD]
[TD="bgcolor: #D8E4BC"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #D8E4BC"]-1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]15/07/2014
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #D8E4BC"]-29878[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]15/07/2014
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #D8E4BC"]-29878[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #D8E4BC"]-1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #D8E4BC"]-1[/TD]
[/TR]
</tbody>[/TABLE]

So multiple questions - is there a better formula to use for this? How do I get the result to show ZERO when both cells are empty? can I get the result to show number of days to present date where there is only a date in first column?

Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

I've used different ranges, but I think this is the sort of thing you want. If not, more details/examples please.

Formula in C4 is copied down.

Excel Workbook
	A	B	C
4
5	16/07/14		4
6	20/06/14	8/07/14	12
7	4/07/14	16/07/14	8
8
9	15/07/14		5
10	15/07/14		5
Allow for blanks

 

[Limbo1964]

That works a treat.

Many thanks for your help.

Paul

 

[Peter_SSs]

That works a treat.

Many thanks for your help.

Paul

Glad it is what you wanted. Thanks for letting us know.

 

[bgerig]

Hello there. My question is for Peter_SSs. I think it is along the same lines as what was asked here but could be wrong. I have an Excel with multiple columns for dates (phases of projects). I need to have one calculation that will show how old each project is just in that phase. I have tried nested IF formulas to no avail. I am trying to get NETWORKDAYS. Any help you could provide would be immensely appreciated. Please see below for an example.


-- removed inline image ---


Example would be showing if Phase 2 is blank, use Phase 1 date through today to show how long it has been there, but one formulate to extend through each phase. Thanks!