Date functions and #value!

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.
 
D

Dave Peterson

=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.
 
S

Shane Devenshire

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))
 
S

SelfTaughExcelJunkie

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top