Date Range

ak255

New Member
Joined
Aug 6, 2007
Messages
5
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!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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
 

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
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
 

ak255

New Member
Joined
Aug 6, 2007
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
How do you know what these numbers will be?
 

ak255

New Member
Joined
Aug 6, 2007
Messages
5

ADVERTISEMENT

What do you mean?
 

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
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
 

ak255

New Member
Joined
Aug 6, 2007
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,912
Members
413,952
Latest member
JGer

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top