nested if for a range of dates

[cjlatta]

I am using Excel 2003 to work on a range of dates. If the date is blank
(null) in a column, I need it to return a 1. Also, in that same column, if
the date is between a date range (we are using a fiscal year from 7-1-xxxx to
6-30-xxxx), I need it to return a 1. If the date in the column is outside
of the fiscal year, I need it to return a 0. We want it to sum the 1's and
0's so we can get a count (summing, of course, I can do!)

I want to expand this formula eventually, to cover other years, but I'd be
happy if I can just figure this part out.
Thanks in advance.

 

[Bob Phillips]

One formula

=SUMPRODUCT(--((B2:B1000="")+((B2:B1000>=--"2007-07-01")*(B2:B1000<=--"2008-06-30"))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Adilson Soledade]

Try this one:
=IF(OR(RefDate<DATE(RefYear,1,7),RefDate>DATE(RefYear,6,30)),0,1)
Explaining:
If the RefDate is outside the range 7-1-xxxx to 6-30-xxxx the formula will
return 0 and in the other case it will return 1.
I consider that RefDate is one range taht contains a date and RefYear is a
cell taht contain the number of the year I'll use to compair with date (ex.
2008, 2007, and so on).