Date functions and #value!

  • Thread starter Thread starter SafetyIntern
  • Start date Start date
S

SafetyIntern

Normally I can hold my own when it comes to Excel, but I am drawing a blank
on this problem and after two days I'm going in circles and am overthinking.
Here is what I have
Column A has a date, which is linked to another spreadsheet, formula is:
=IF(ISBLANK('P:\[2009 COMEX CHART.xls]FEB'!$B7),"-",('P:\[2009 COMEX
CHART.xls]FEB'!$B7))
Column C should be the next business day, and was working fine, but if a
date is not in column A i get the dreaded #value!; column c's formula is:
=A34+IF(WEEKDAY(A34+1)=7,3,1)

My question is how can I get column C to stay blank if column A is blank. I
tried the isblank with the formula, but I must not be typing it correctly
because I get an error message that says I've entered too many arguments.

Thanks for the help.
 
=if(a34="","",A34+IF(WEEKDAY(A34+1)=7,3,1))

Or maybe better:

=if(isnumber(a34),A34+IF(WEEKDAY(A34+1)=7,3,1),"")




Normally I can hold my own when it comes to Excel, but I am drawing a blank
on this problem and after two days I'm going in circles and am overthinking.
Here is what I have
Column A has a date, which is linked to another spreadsheet, formula is:
=IF(ISBLANK('P:\[2009 COMEX CHART.xls]FEB'!$B7),"-",('P:\[2009 COMEX
CHART.xls]FEB'!$B7))
Column C should be the next business day, and was working fine, but if a
date is not in column A i get the dreaded #value!; column c's formula is:
=A34+IF(WEEKDAY(A34+1)=7,3,1)

My question is how can I get column C to stay blank if column A is blank. I
tried the isblank with the formula, but I must not be typing it correctly
because I get an error message that says I've entered too many arguments.

Thanks for the help.
Click to expand...
 
Hi,

the problem is occuring because you return "-" if the cell is blank. To
handle this

change =A34+IF(WEEKDAY(A34+1)=7,3,1)

to
=IF(A34="-","",A34+IF(WEEKDAY(A34+1)=7,3,1))
 
This one worked like a charm...thank you so much

Shane Devenshire said:
Hi,

the problem is occuring because you return "-" if the cell is blank. To
handle this

change =A34+IF(WEEKDAY(A34+1)=7,3,1)

to
=IF(A34="-","",A34+IF(WEEKDAY(A34+1)=7,3,1))
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


SafetyIntern said:
Normally I can hold my own when it comes to Excel, but I am drawing a blank
on this problem and after two days I'm going in circles and am overthinking.
Here is what I have
Column A has a date, which is linked to another spreadsheet, formula is:
=IF(ISBLANK('P:\[2009 COMEX CHART.xls]FEB'!$B7),"-",('P:\[2009 COMEX
CHART.xls]FEB'!$B7))
Column C should be the next business day, and was working fine, but if a
date is not in column A i get the dreaded #value!; column c's formula is:
=A34+IF(WEEKDAY(A34+1)=7,3,1)

My question is how can I get column C to stay blank if column A is blank. I
tried the isblank with the formula, but I must not be typing it correctly
because I get an error message that says I've entered too many arguments.

Thanks for the help.
Click to expand...
Click to expand...
 
Back
Top