nested if for a range of dates
 

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.

nested if for a range of dates
 

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)



"cjlatta" wrote in message
...
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.

nested if for a range of dates
 

Try this one:
=IF(OR(RefDate<DATE(RefYear,1,7),RefDateDATE(RefY ear,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).
--
Adilson Soledade


"cjlatta" wrote:

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.