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 |
=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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 11:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com