New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Filtering the Previous date

Macros, VBA, Excel Automation, etc.

Filtering the Previous date

Postby pkm » Thu Apr 21, 2016 12:51 am

Hi I want to Filter the previous(Yesterday's) date using Excel Macro
e.g. (today(21/4/2015),yesterday(20/4/2015)).In this I want to filter 20/4/2015.
In this one condition, If Monday comes we want to filter Friday date because Saturday & Sunday no data will be there to filter on that date. So at Monday we want to filter Friday date. For Tuesday, Wednesday, Thursday, Friday we want to filter previous day date.
So please give me solution.
  • 0

pkm
Rookie
 
Posts: 1
Joined: Apr 21, 2016
Reputation: 0
Excel Version: 2013

Re: Filtering the Previous date

Postby NoSparks » Thu Apr 21, 2016 9:54 am

If your week is Sunday to Saturday,
3 is the maximum number of days to go back,
going back 1 day is built in so the max to subtract is another 2.
try something along the lines of this, the message box is just to see the result

Code: Select all
FilterCriteria = Date - 1
For i = 1 To 2
    If Day(FilterCriteria) = 1 Or Day(FilterCriteria) = 7 Then
        FilterCriteria = FilterCriteria - i
    Else
        Exit For
    End If
Next i
MsgBox FilterCriteria
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 47 guests