Subtracting Dates with Empty Cells
 

I am using this formula to figure out how many days are between two dates
without including the weekends.
=NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15.

It works very well, however in cell N15 the columns I am subtracting (cell
J15 and column K15) are missing dates. Could you tell me what I could
include in this formula so that it does not work when a cell in column J
and/or column K is missing a date? What I would like to happen instead is
for cell N15 (which contains the formula) to be blank or contain a zero
inside of it, when dates are missing from J15 and/or K15. Is this possible?

Subtracting Dates with Empty Cells
 

Add this to the beginning of the formula:

=IF(COUNT(J15:K15)<2,0,

And add a closing ")" to the very end of the formula

--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
I am using this formula to figure out how many days are between two dates
without including the weekends.
=NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).
I have placed this formula in cell N15.

It works very well, however in cell N15 the columns I am subtracting (cell
J15 and column K15) are missing dates. Could you tell me what I could
include in this formula so that it does not work when a cell in column J
and/or column K is missing a date? What I would like to happen instead is
for cell N15 (which contains the formula) to be blank or contain a zero
inside of it, when dates are missing from J15 and/or K15. Is this
possible?

Subtracting Dates with Empty Cells
 

One way:

=IF(COUNT(J15:K15)<2,"",<your formula here)


In article ,
Workbook wrote:

I am using this formula to figure out how many days are between two dates
without including the weekends.
=NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(
K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15.

It works very well, however in cell N15 the columns I am subtracting (cell
J15 and column K15) are missing dates. Could you tell me what I could
include in this formula so that it does not work when a cell in column J
and/or column K is missing a date? What I would like to happen instead is
for cell N15 (which contains the formula) to be blank or contain a zero
inside of it, when dates are missing from J15 and/or K15. Is this possible?

Subtracting Dates with Empty Cells
 

=IF(COUNT(J15:K15)<2,0,NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).")"
was not working so I tried =IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1)
instead and had some success because the cell went blank, but I realized I
need for the cell to change to "0", otherwise other formulas get fowled up.
I also noticed next to the cells that didn't go blank an exclamation point
that when I clicked it gave me this message"The formula in this cell refers
to a range that has additional numbers adjacent to it." Do you have any
thoughts? Thank you for your feedback.



"T. Valko" wrote:

Add this to the beginning of the formula:

=IF(COUNT(J15:K15)<2,0,

And add a closing ")" to the very end of the formula

--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
I am using this formula to figure out how many days are between two dates
without including the weekends.
=NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).
I have placed this formula in cell N15.

It works very well, however in cell N15 the columns I am subtracting (cell
J15 and column K15) are missing dates. Could you tell me what I could
include in this formula so that it does not work when a cell in column J
and/or column K is missing a date? What I would like to happen instead is
for cell N15 (which contains the formula) to be blank or contain a zero
inside of it, when dates are missing from J15 and/or K15. Is this
possible?

Subtracting Dates with Empty Cells
 

Thank you for your input.
=IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6))
was not working and I would get a message that said "#VALUE! in the cell, so
I tried =IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1) instead and had some
success because the cell went blank. However, I realized I need for the cell
to change to "0", otherwise other formulas get fowled up. I also noticed
next to the cells that didn't go blank an exclamation point that when I held
my pointer over it I got the message"The formula in this cell refers to a
range that has additional numbers adjacent to it." What do you think?


"JE McGimpsey" wrote:

One way:

=IF(COUNT(J15:K15)<2,"",<your formula here)


In article ,
Workbook wrote:

I am using this formula to figure out how many days are between two dates
without including the weekends.
=NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(
K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15.

It works very well, however in cell N15 the columns I am subtracting (cell
J15 and column K15) are missing dates. Could you tell me what I could
include in this formula so that it does not work when a cell in column J
and/or column K is missing a date? What I would like to happen instead is
for cell N15 (which contains the formula) to be blank or contain a zero
inside of it, when dates are missing from J15 and/or K15. Is this possible?

Subtracting Dates with Empty Cells
 

=IF(COUNT(J15:K15)<2,
=IF(COUNT(J15:K15)<2,

There is essentially no difference between the 2 of those expressions.

I see at the end of the formula you have:

......)<6).")"

It should be:

......)<6))

About that message, I would just ignore it. I have all those error checking
messages turned off.


--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
=IF(COUNT(J15:K15)<2,0,NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).")"
was not working so I tried
=IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1)
instead and had some success because the cell went blank, but I realized I
need for the cell to change to "0", otherwise other formulas get fowled
up.
I also noticed next to the cells that didn't go blank an exclamation point
that when I clicked it gave me this message"The formula in this cell
refers
to a range that has additional numbers adjacent to it." Do you have any
thoughts? Thank you for your feedback.



"T. Valko" wrote:

Add this to the beginning of the formula:

=IF(COUNT(J15:K15)<2,0,

And add a closing ")" to the very end of the formula

--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
I am using this formula to figure out how many days are between two
dates
without including the weekends.
=NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).
I have placed this formula in cell N15.

It works very well, however in cell N15 the columns I am subtracting
(cell
J15 and column K15) are missing dates. Could you tell me what I could
include in this formula so that it does not work when a cell in column
J
and/or column K is missing a date? What I would like to happen instead
is
for cell N15 (which contains the formula) to be blank or contain a zero
inside of it, when dates are missing from J15 and/or K15. Is this
possible?

Subtracting Dates with Empty Cells
 

Thanks Man. I must be typing something incorrectly. However, I am having
success with this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1),
so I'll probably stick with it. Thank you again for your input!

"T. Valko" wrote:

=IF(COUNT(J15:K15)<2,
=IF(COUNT(J15:K15)<2,

There is essentially no difference between the 2 of those expressions.

I see at the end of the formula you have:

......)<6).")"

It should be:

......)<6))

About that message, I would just ignore it. I have all those error checking
messages turned off.


--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
=IF(COUNT(J15:K15)<2,0,NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).")"
was not working so I tried
=IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1)
instead and had some success because the cell went blank, but I realized I
need for the cell to change to "0", otherwise other formulas get fowled
up.
I also noticed next to the cells that didn't go blank an exclamation point
that when I clicked it gave me this message"The formula in this cell
refers
to a range that has additional numbers adjacent to it." Do you have any
thoughts? Thank you for your feedback.



"T. Valko" wrote:

Add this to the beginning of the formula:

=IF(COUNT(J15:K15)<2,0,

And add a closing ")" to the very end of the formula

--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
I am using this formula to figure out how many days are between two
dates
without including the weekends.
=NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).
I have placed this formula in cell N15.

It works very well, however in cell N15 the columns I am subtracting
(cell
J15 and column K15) are missing dates. Could you tell me what I could
include in this formula so that it does not work when a cell in column
J
and/or column K is missing a date? What I would like to happen instead
is
for cell N15 (which contains the formula) to be blank or contain a zero
inside of it, when dates are missing from J15 and/or K15. Is this
possible?

Subtracting Dates with Empty Cells
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
Thanks Man. I must be typing something incorrectly. However, I am having
success with this formula
=IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1),
so I'll probably stick with it. Thank you again for your input!

"T. Valko" wrote:

=IF(COUNT(J15:K15)<2,
=IF(COUNT(J15:K15)<2,

There is essentially no difference between the 2 of those expressions.

I see at the end of the formula you have:

......)<6).")"

It should be:

......)<6))

About that message, I would just ignore it. I have all those error
checking
messages turned off.


--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
=IF(COUNT(J15:K15)<2,0,NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).")"
was not working so I tried
=IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1)
instead and had some success because the cell went blank, but I
realized I
need for the cell to change to "0", otherwise other formulas get fowled
up.
I also noticed next to the cells that didn't go blank an exclamation
point
that when I clicked it gave me this message"The formula in this cell
refers
to a range that has additional numbers adjacent to it." Do you have
any
thoughts? Thank you for your feedback.



"T. Valko" wrote:

Add this to the beginning of the formula:

=IF(COUNT(J15:K15)<2,0,

And add a closing ")" to the very end of the formula

--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
I am using this formula to figure out how many days are between two
dates
without including the weekends.
=NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).
I have placed this formula in cell N15.

It works very well, however in cell N15 the columns I am subtracting
(cell
J15 and column K15) are missing dates. Could you tell me what I
could
include in this formula so that it does not work when a cell in
column
J
and/or column K is missing a date? What I would like to happen
instead
is
for cell N15 (which contains the formula) to be blank or contain a
zero
inside of it, when dates are missing from J15 and/or K15. Is this
possible?