Looking for a formula to count dates in between a range. 1

[Snooker07]

Hello,

I am looking for a formula to count dates in between a range.
for example I have a colum with dates ranging from 11/17/2008 to 12/31/2008.

Over on the next colum I want to say..if any of these dates are between 11/17/2008 and 11/24/2008 then give me a 1, or nothing.

This way I can easily have a total number of dates between that range.

Thank you so much.

Snooker07

 

[johnisotank]

Lets say all your dates to evaluate are in column A.
You have your date from in cell E1 (11/17/2008)
You have your date to in cell F1 (12/31/2008)

Your formula in cell B1 would be:

=IF(AND(A1>=$E$1,A1<=$F$1)=TRUE,1,0)

Drag this down and it will evaluate each cell.

John

 

[SkipVought]

Hi,


I have a list os data Range Named [/b]MyDates[/b] with your dates as specified
I have a cell named From containing 11/17/2008
I have a cell named Thru containing 11/24/2008
[tt]
=SUMPRODUCT(--(MyDates>=From)*(MyDates<=Thru))
[/tt]
VOLA!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Snooker07]

GOT IT...Thank you so much!!!

 

[SkipVought]

Will you please specify what worked for you. Other members, browsing this site, will find it helpful and informative. Tek-Tips is members helping members.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Snooker07]

Sure thing,
the formula I actually used is the following.

=IF(L10>39763,IF(L10<39782,1,""))

This gave me a "1" or a "False".....then I summed the Colum to get my count.

I came accross this formula before anyone replied.

Thank you.

 

[johnisotank]

Hi Snooker,

I would recommend you look into Skips proposal. His will give you the count in one tidy cell instead of my (and your) way of having to put a 1 or false into lots and lots of cells - looks unnecessarily untidy...



John

 

[dhookom]

I don't hang out here much but Skip's solution will have me coming back for more. Definitely deserves a star.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Duane, I'm HOOKED on Excel. ;-)

I sure appreciate your contributions in the MS Access forums! Thanks!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I've been creating quite a few solutions based on Excel presentation/reporting of data from SQL Server. I actually had a file with a named range of dates and named cells for Start and End. Once I got the ()s correct, your expression worked as advertised.

=SUMPRODUCT(--(DateRange>=Start) * (DateRange<=End))

Maybe some day there'll be Hook'D on Excel.

Duane
Hook'D on Access
MS Access MVP