If then functions on a date

  • Thread starter Thread starter smorzando
  • Start date Start date
S

smorzando

i wrote late last week about using an if statement to pull
data out of one worksheet onto another.

i've succeeded in pulling data correctly, if the formula
is basic and uses a = (e.g. IF('Input Sheet'!B7="M",'Input
Sheet'!A7,"") but i can-t get the formula to work with
dates, or when i want to use <>.

what i would like to do on the Input Sheet B column, is
input dates, such as 10/1/03.

then in the Master Workbook, I would like to pull the data
that is associated with the dates between 10/1/03 and
10/31/03. I imagine this formula to read something like :
IF('Input Sheet'!B7<"10/03",'Input Sheet'!A7,""). or using
an OR statement and providing the dates of 10/1/03 and
10/31/03. but it doesn't seem to work this way.

please advise, and thanks again for the previous help and
forthcoming help.
 
Hi:

Dates in text form will be interpreted as test. Try something like:

IF('Input Sheet'!B7<DATEVALUE("10/01/03"), ... )

Regards,

Vasant.
 
Thanks much. I've got that to work properly, which is a
step. Now, how do I go about checking to see if data falls
within a date range, say between 10/01/03 and 10/31/03?
is there a DATERANGE argument that can be used in an IF
statement?

or do I need to do an OR to capture the date range?
 
You could use an Or:
=if(or(a1<date(2003,10,01),a1>date(2003,10,31)),"Not Oct 2003","oct 2003")

or an And:
=if(and(a1>=date(2003,10,01),a1<=date(2003,10,31)),"oct 2003","not oct 2003")

or
=if(and(month(a1)=10,year(a1)=2003),"oct 2003","not oct 2003")

or even a variation of:
=if(text(a1,"mmyyyy")="102003","oct 2003","not oct 2003")

(Yeah, I like =date() and Vasant likes =DateValue().)
 
Thanks.

this is working wonderfully.

using the formula (with date or datevalue), i've been able
to correctly pull the data from the Input Sheet that falls
within the date range.

=IF(OR('Input Sheet'!B7<DATE(2003,1,1),'Input Sheet'!
B7>DATE(2003,1,31)), "",'Input Sheet'!A7)

for each month of 2004, i've created a worksheet. each
worksheet pulls the data from the Input Sheet that
corresponds with the date. This is working well, now. But,
now, as I've copied this formula down the column grabbing
data from the cell in column B from the Input Sheet, when
appropriate, and leaving the cell blank when the date
falls outside of the range.

Thus, my worksheets show data and then have blank lines,
and show data again. Is there any way to write the formula
so that I can avoid the blank lines?

and could someone recommend a book or a website that would
help out with these formulae? i have other programs that
use similar language and i'm only comfortable creating
simple formulas.
 
if i then sorted or the data by date or ran macro , it
should weed out all the blank lines, right?

is this the simple answer?
 
Try it and see!

Another option maybe to just filter your data. Only show the rows that do not
look blank.

Can you pick out a column that always has data?

If yes,
select your range
data|filter|autofilter

Then use the dropdown on that column and choose "non-blanks".
 
Back
Top