date question?

nikko50

Board Regular
Joined
Mar 3, 2004
Messages
155
So if I take todays date and I want to loop thru column A that has various date/time records formatted like "10/3/2014 12:15:23 PM" and pull only the records that our within 7 days of todays date how can I do that. I will put those values into column B but I'm hung up on the date thing.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Code:
Sub loopingDate()

    Dim nextRow As Long
    nextRow = 2


    For x = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        Select Case Cells(x, 1)
        
            Case Is < Date - 7
            Case Is > Date + 7
            Case Else
                Cells(nextRow, 2) = Cells(x, 1)
                nextRow = nextRow + 1
        End Select
    Next x
End Sub
 
Upvote 0
Excel 2012
A
B
C
D
1
My Dates
Within 7 Days?
2
10/3/2014 12:15
TRUE
3
10/5/2014 12:15
TRUE
4
10/21/2014 12:15
FALSE
5
9/3/2014 12:15
FALSE
6
9/28/2014 12:15
TRUE

<TBODY>
</TBODY>
Sheet1

Not your final answer, but does this assist you?

Worksheet Formulas
Cell
Formula
D2
=AND(INT(A2)>=TODAY()-7,INT(A2)<=TODAY()+7)

<TBODY>
</TBODY>


<TBODY>
</TBODY>
 
Upvote 0
Nextrow accumulate must be "outside" the conditional Loop.

Rich (BB code):
Sub loopingDate()
Dim nextRow As Long
nextRow = 2

For x = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Select Case Cells(x, 1)

Case Is < Date - 7
Case Is > Date + 7
Case Else
Cells(nextRow, 2) = Cells(x, 1)
End Select
nextRow = nextRow + 1
Next x
End Sub
 
Upvote 0
Nextrow accumulate must be "outside" the conditional Loop.

Rich (BB code):
Sub loopingDate()
Dim nextRow As Long
nextRow = 2

For x = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Select Case Cells(x, 1)

Case Is < Date - 7
Case Is > Date + 7
Case Else
Cells(nextRow, 2) = Cells(x, 1)
End Select
nextRow = nextRow + 1
Next x
End Sub


You only want it to increase by one when it finds a match, os it needs to be in the same conditional. It has no impact on the value of x...or I would use x.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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
Back
Top