Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Walter
 
Posts: n/a
Default 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   Report Post  
CarlosAntenna
 
Posts: n/a
Default

=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   Report Post  
JulieD
 
Posts: n/a
Default

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   Report Post  
Walter
 
Posts: n/a
Default

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   Report Post  
Walter
 
Posts: n/a
Default

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   Report Post  
JulieD
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
how do you ignore blank cells Kerry Excel Discussion (Misc queries) 1 February 16th 05 02:55 PM
Eliminate creating list that returns blank cells Marc Todd Excel Worksheet Functions 1 January 26th 05 10:58 PM
COUNT ONLY CELLS THAT AREN'T BLANK paulinec Excel Worksheet Functions 8 January 8th 05 02:51 AM
In Exel 2000, stop the blank cells (with formulas) from printing. tonyoc Excel Discussion (Misc queries) 1 December 10th 04 01:38 AM


All times are GMT +1. The time now is 01:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"