Date Range

[ak255]

Hello,
I need to find out a way to make a date range out of a starting date and an ending date
i.e.

a1 6/25/2007
a2 7/4/2007

what i want then is for collumn b to have all the date inbetween those two dates written out

b1 6/25/2007
b2 6/26/2007
b3 6/27/2007 etc.

I am using this formula in a macro, so each date range is going to be different in size, so I need a formula that is flexible for all date ranges

Thanks!

 

[Lewiy]

Try this:

Sub DateRange()
Dim startdate As Date
Dim enddate As Date
Dim c As Date
Dim d As Long
startdate = Range("A1")
enddate = Range("A2")
d = 1
For c = startdate To enddate Step 1
    Cells(d, 2) = c
    d = d + 1
Next c
End Sub

 

[goldfish]

Welcome!
Since you said your alread using a macro, I'll give you a macro solution:

Sub PlaceDates()
Dim DateCount As Long, StartDate As Long, EndDate As Long
StartDate = Int(Range("A1"))
EndDate = Int(Range("A2"))
For DateCount = StartDate To EndDate
Range("B:B")(DateCount - StartDate + 1) = DateCount
Next DateCount
Columns("B:B").NumberFormat = "m/d/yyyy"
End Sub

HTH,
~Gold Fish

 

[ak255]

Thank you!

 

[ak255]

I also have another collumn of data next to the collumn of dates. The number in that column corespond to the number of mail coming in on that day. Those number come from a formula I drag down. Is there a way to have a macro that also drags down this column the same number of rows it dragged down the dates?

 

[Lewiy]

How do you know what these numbers will be?

 

[ak255]

What do you mean?

 

[goldfish]

[Deleted]

 

[goldfish]

Assuming you put the formula into C1 then use:

Sub PlaceDates()
Dim DateCount As Long, StartDate As Long, EndDate As Long, FormulaString As String
StartDate = Int(Range("A1"))
EndDate = Int(Range("A2"))
FormulaString = Range("C1").Formular1c1
For DateCount = StartDate To EndDate
    Range("B:B")(DateCount - StartDate + 1) = DateCount
    Range("C:C")(DateCount - StartDate + 1).Formular1c1 = FormulaString
Next DateCount
Columns("B:B").NumberFormat = "m/d/yyyy"
End Sub

HTH,
~Gold Fish

(It might be better to write this code with the formula in it, in case C1 ever gets deleted, this is the approach that Lewiy was trying to use, but didn't know what formula you wanted, so couldn't)

~Gold Fish

 

[ak255]

C1 I put the formula =countif(D:D,B1)
and in C2 I put the formula =countif(D:D,B2)+C1 and then drag down

What I want the macro to do is to drag down the formula from C2 the same amount of row it dragged down the dates.

The problem I came across was that I had two different formula in C1 and C2, because I need the total of all the mail that came in to date as of the date mentioned in collumn B. Is there any other way to do this?

Thanks!