Counting Between Dates

[tjolson]

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

 

[E Oveson]

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

 

[Opinicus]

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?

 

[E Oveson]

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