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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,483
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>
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,483
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
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,483
Your code is nice, I was going for a different report look.

Jim
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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