Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If function with blank cells
I have a worksheet with a date column for the week ending date. I want this
to automaticaly enter the date and calculate the row when the date meets today's date. I have the following formula, which works to 1 row below last week's ending date and then it gives #VALUE. =IF(A12+7<TODAY(),A12+7,"") I tried nesting an OR function to test for blank cells but still get the #VALUE. =IF(OR(A12="",A12+7TODAY()),"",A12+7) How can I eliminate this? -- Thanks for your help, Walter |
#2
|
|||
|
|||
=IF(ISBLANK(A12),"",IF(A12+7<TODAY(),A12+7,""))
--Carlos "Walter" wrote in message ... I have a worksheet with a date column for the week ending date. I want this to automaticaly enter the date and calculate the row when the date meets today's date. I have the following formula, which works to 1 row below last week's ending date and then it gives #VALUE. =IF(A12+7<TODAY(),A12+7,"") I tried nesting an OR function to test for blank cells but still get the #VALUE. =IF(OR(A12="",A12+7TODAY()),"",A12+7) How can I eliminate this? -- Thanks for your help, Walter |
#3
|
|||
|
|||
Hi Walter
this works for me =IF(OR(A12="",A12+7TODAY()),"",A12+7) you might like to deal with the situation where A12+7 = today as neither of your formulas took that into account e.g. =IF(OR(A12="",A12+7=TODAY()),"",A12+7) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Walter" wrote in message ... I have a worksheet with a date column for the week ending date. I want this to automaticaly enter the date and calculate the row when the date meets today's date. I have the following formula, which works to 1 row below last week's ending date and then it gives #VALUE. =IF(A12+7<TODAY(),A12+7,"") I tried nesting an OR function to test for blank cells but still get the #VALUE. =IF(OR(A12="",A12+7TODAY()),"",A12+7) How can I eliminate this? -- Thanks for your help, Walter |
#4
|
|||
|
|||
THanks Carlos. That works.
-- Thanks for your help, Walter "CarlosAntenna" wrote: =IF(ISBLANK(A12),"",IF(A12+7<TODAY(),A12+7,"")) --Carlos "Walter" wrote in message ... I have a worksheet with a date column for the week ending date. I want this to automaticaly enter the date and calculate the row when the date meets today's date. I have the following formula, which works to 1 row below last week's ending date and then it gives #VALUE. =IF(A12+7<TODAY(),A12+7,"") I tried nesting an OR function to test for blank cells but still get the #VALUE. =IF(OR(A12="",A12+7TODAY()),"",A12+7) How can I eliminate this? -- Thanks for your help, Walter |
#5
|
|||
|
|||
I couldn't see any difference in your formula from what I had tried:
=IF(OR(A12="",A12+7TODAY()),"",A12+7). Carlos suggested using the ISBLANK function which works. Thanks for catching the "not dealing with today's date". I hadn't caught that. It always helps for another set of eyes to look at things. I really appreciate your time to help me! -- Thanks for your help, Walter "JulieD" wrote: Hi Walter this works for me =IF(OR(A12="",A12+7TODAY()),"",A12+7) you might like to deal with the situation where A12+7 = today as neither of your formulas took that into account e.g. =IF(OR(A12="",A12+7=TODAY()),"",A12+7) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Walter" wrote in message ... I have a worksheet with a date column for the week ending date. I want this to automaticaly enter the date and calculate the row when the date meets today's date. I have the following formula, which works to 1 row below last week's ending date and then it gives #VALUE. =IF(A12+7<TODAY(),A12+7,"") I tried nesting an OR function to test for blank cells but still get the #VALUE. =IF(OR(A12="",A12+7TODAY()),"",A12+7) How can I eliminate this? -- Thanks for your help, Walter |
#6
|
|||
|
|||
Hi Walter
what i'm saying is that your formula should have worked for you ... and was suggesting that you try it again - but if you've got a working formula that's all that really matters. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Walter" wrote in message ... I couldn't see any difference in your formula from what I had tried: =IF(OR(A12="",A12+7TODAY()),"",A12+7). Carlos suggested using the ISBLANK function which works. Thanks for catching the "not dealing with today's date". I hadn't caught that. It always helps for another set of eyes to look at things. I really appreciate your time to help me! -- Thanks for your help, Walter "JulieD" wrote: Hi Walter this works for me =IF(OR(A12="",A12+7TODAY()),"",A12+7) you might like to deal with the situation where A12+7 = today as neither of your formulas took that into account e.g. =IF(OR(A12="",A12+7=TODAY()),"",A12+7) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Walter" wrote in message ... I have a worksheet with a date column for the week ending date. I want this to automaticaly enter the date and calculate the row when the date meets today's date. I have the following formula, which works to 1 row below last week's ending date and then it gives #VALUE. =IF(A12+7<TODAY(),A12+7,"") I tried nesting an OR function to test for blank cells but still get the #VALUE. =IF(OR(A12="",A12+7TODAY()),"",A12+7) How can I eliminate this? -- Thanks for your help, Walter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clock | Excel Worksheet Functions | |||
how do you ignore blank cells | Excel Discussion (Misc queries) | |||
Eliminate creating list that returns blank cells | Excel Worksheet Functions | |||
COUNT ONLY CELLS THAT AREN'T BLANK | Excel Worksheet Functions | |||
In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) |