Counting Between Dates

Hello All,
I know this question seems redundant, but I can't seem to find a pos
that resembles the question I have. I am trying to count the number o
days between to dates through a 10 year, daily period. I have a colum
of excursion periods that range from 10 days to 15 days to 75 days
etc. Every period has a different number of days. Instead o
scrolling down to each period and simply counting the cells betwee
dates, I would like to write a function that will do all the summin
for me. Is this a countif nested in an IF block?
I'll try to say this again in a different way: I need to count th
number of days between two dates until the next start date i
achieved.

Col A Col B
# 1/1/04
#
#
#
# 1/5/04
#
#
#
#
#
#
#
#
#
#
# 1/16/04

So I need to sum the cells starting at 1/1/04 untill the next date i
column B. Then, start summing the cells between 1/5/04 and 1/16/04 an
so on down the column.
As you can see, there are different numbers of days for each excursio
period. I have 10 years of data (a range of almost 4000 days) and don
feel like summing for each individual range of dates. Any help fro
anyone would be greatly appreciated. Thank

--
tjolso
-----------------------------------------------------------------------
tjolson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1615
View this thread: http://www.excelforum.com/showthread.php?threadid=27593

E
Sat Nov 06 16:36:52 CST 2004

Hi,
Assuming your dates start at B1 (change below if need to) and you want the
sum days in Column C, this should do what you want:

Sub sumDays()
Dim rngCol As Range, rng As Range, prevRng As Range

Set rngCol = Columns(2).SpecialCells(xlCellTypeConstants)
Set prevRng = Range("B1")

For Each rng In rngCol
Cells(rng.Row, 3).Value = rng.Row - prevRng.Row
Set prevRng = rng
Next rng

End Sub

-Erik

"tjolson" <tjolson.1fbjwm@excelforum-nospam.com> wrote in message
news:tjolson.1fbjwm@excelforum-nospam.com...
>
> Hello All,
> I know this question seems redundant, but I can't seem to find a post
> that resembles the question I have. I am trying to count the number of
> days between to dates through a 10 year, daily period. I have a column
> of excursion periods that range from 10 days to 15 days to 75 days,
> etc. Every period has a different number of days. Instead of
> scrolling down to each period and simply counting the cells between
> dates, I would like to write a function that will do all the summing
> for me. Is this a countif nested in an IF block?
> I'll try to say this again in a different way: I need to count the
> number of days between two dates until the next start date is
> achieved.
>
> Col A Col B
> # 1/1/04
> #
> #
> #
> # 1/5/04
> #
> #
> #
> #
> #
> #
> #
> #
> #
> #
> # 1/16/04
>
> So I need to sum the cells starting at 1/1/04 untill the next date in
> column B. Then, start summing the cells between 1/5/04 and 1/16/04 and
> so on down the column.
> As you can see, there are different numbers of days for each excursion
> period. I have 10 years of data (a range of almost 4000 days) and dont
> feel like summing for each individual range of dates. Any help from
> anyone would be greatly appreciated. Thanks
>
>
> --
> tjolson
> ------------------------------------------------------------------------
> tjolson's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=16159
> View this thread: http://www.excelforum.com/showthread.php?threadid=275938
>

Opinicus
Sat Nov 06 23:05:44 CST 2004

"E Oveson" <eoveREMOVEITson@hotmail.com> wrote

> Assuming your dates start at B1 (change below if need to)
> and you want the sum days in Column C, this should do what
> you want:
> Sub sumDays()
> Dim rngCol As Range, rng As Range, prevRng As Range
> Set rngCol = Columns(2).SpecialCells(xlCellTypeConstants)
> Set prevRng = Range("B1")
> For Each rng In rngCol
> Cells(rng.Row, 3).Value = rng.Row - prevRng.Row
> Set prevRng = rng
> Next rng
> End Sub

I'm very new at this. How does one enter VB code like the
above into an Excel worksheet? And how is it activated?

--
Bob
Kanyak's Doghouse
http://www.kanyak.com

E
Sat Nov 06 23:31:40 CST 2004

Hi Bob,

Right-Click the sheet tab and choose "View Code" and then paste the code
where the cursor is. Then you can run that code by pressing F5 (while in
the Visual Basic Editor) or by going back to the workbook and choosing it
under Tools->Macro->Macros...->choosing the macro and clicking Run. If you
have any problem or need the code modified to fit the layout of your sheet
let me know.

-Erik

"Opinicus" <gezgin@spamcop.net> wrote in message
news:10orb9bemc64e01@news.supernews.com...
> "E Oveson" <eoveREMOVEITson@hotmail.com> wrote
>
>> Assuming your dates start at B1 (change below if need to) and you want
>> the sum days in Column C, this should do what you want:
>> Sub sumDays()
>> Dim rngCol As Range, rng As Range, prevRng As Range
>> Set rngCol = Columns(2).SpecialCells(xlCellTypeConstants)
>> Set prevRng = Range("B1")
>> For Each rng In rngCol
>> Cells(rng.Row, 3).Value = rng.Row - prevRng.Row
>> Set prevRng = rng
>> Next rng
>> End Sub
>
> I'm very new at this. How does one enter VB code like the above into an
> Excel worksheet? And how is it activated?
>
> --
> Bob
> Kanyak's Doghouse
> http://www.kanyak.com
>