Date Range

ak255

New Member
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!

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Try this:
Code:
``````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``````

Welcome!
Since you said your alread using a macro, I'll give you a macro solution:
Code:
``````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

Thank you!

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?

How do you know what these numbers will be?

What do you mean?

[Deleted]

Assuming you put the formula into C1 then use:
Code:
``````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

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!

Replies
1
Views
54
Replies
8
Views
139
Replies
1
Views
78
Replies
7
Views
198
Replies
1
Views
256

1,217,673
Messages
6,137,916
Members
450,098
Latest member
Ikmal Sabri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back