Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date functions and #value!
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date functions and #value!
=if(a34="","",A34+IF(WEEKDAY(A34+1)=7,3,1))
Or maybe better: =if(isnumber(a34),A34+IF(WEEKDAY(A34+1)=7,3,1),"") SafetyIntern wrote: 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. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date functions and #value!
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" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date functions and #value!
This one worked like a charm...thank you so much
"Shane Devenshire" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date functions | Excel Worksheet Functions | |||
Date functions | Excel Worksheet Functions | |||
Date Functions | Excel Discussion (Misc queries) | |||
Date Functions | Excel Discussion (Misc queries) | |||
Date functions | Excel Worksheet Functions |