Please Help - Criteria wording - Three (3) date ranges in for advance filter.

J

joach

New Member
Joined
Jul 15, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm wanting to use advance filters to filter my data set.

I am wanting to follow up with the customer 8 months, 20 months and 56 months after the purchase date, but if the date is equal to or more than 12, 24 and 60 months from the purchase date I don't want to see the data in the new sheet.

In written terms I want to see the following data however I don't know how to type it in an Excel format / formulas.

follow up
1 year if todays date is between 8 months and 12 months from the purchase date
2 yearif todays date is between 20 months and 24 months from the purchase date
5 yearif todays date is between 56 months and 60 months from the purchase date

Could you please help me with the formatting / formulas for the above criteria.

In addition to this once the filtered data is on the new sheet 'follow up' I want to use conditional formatting with a colour gradient to show the following - green being the lower number (i.e 8 months), yellow being the mid point (i.e 10 months) and red being the deadline (i.e 12months).

If you could please help with the above that would be amazing.

If you need anything else please let me know.
 
Sort by date Sort by votes
Alex Blakenburg said:
If you don't have something that looks like these 3 lines in your code then it would cause your error,

VBA Code: 
    Dim shtActiveSalesCashless As Worksheet                                 ' Declare the variable and the variable type
       
    Set shtActiveSalesCashless = Worksheets("PutInTheCorrectSheetName")     ' Point the Varable at the sheet
   
    With shtActiveSalesCashless                                             ' Using the Variable in the code
Click to expand...
I have those three sections updated in the code... I tried without spaces too however that resulted in bugs.
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Alex Blakenburg said:
Give the code below a try. You will need to put it into a standard / regular VBA module.


The (No Cash) sheet name can't exist it is too long.
Create a sheet called "Follow Up 12 21 57 (No Cash)" that is what I have used in this code.

If you don't know what to do, you might want to read this first:
VBA Code

VBA Code: 
Sub CreateFollowUpSheets()

    ' Create 2 new sheets that meet follow up criteria
    ' Primary criteria Purchase date is within 4 months before the 1st, 2nd and 5th anniversary
 
    Dim shtSrc As Worksheet, shtFollowUp As Worksheet, shtFollowNoCsh As Worksheet
    Dim tblSrc As ListObject
    Dim rngSrc As Range, rngDest As Range, rngCrit As Range
    Dim srcRowFirst As Long, srcRowLast As Long, srcColLast As Long
    Dim strFormula As String

    Application.ScreenUpdating = False
    
    srcRowFirst = 1
 
    Set shtSrc = Worksheets("Filtered Orders")
    Set shtFollowUp = Worksheets("Follow Up 12 21 57 Months")
    Set shtFollowNoCsh = Worksheets("Follow Up 12 21 57 (No Cash)")
 
    Set tblSrc = Range("Filtered_Orders").ListObject

    With shtSrc
        srcColLast = tblSrc.Range.Columns(1).Column + tblSrc.Range.Columns.Count - 1
        Set rngSrc = tblSrc.Range
    End With
 
    strFormula = "=IF(OR("
    strFormula = strFormula _
                    & "AND($K2<=TODAY()," _
                    & "$K2>DATE(YEAR(TODAY())," & "MONTH(TODAY())-4,DAY(TODAY()))),"
    strFormula = strFormula _
                    & "AND($K2<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))," _
                    & "$K2>DATE(YEAR(TODAY())-1,MONTH(TODAY())-4,DAY(TODAY()))),"
    strFormula = strFormula _
                    & "AND($K2<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY()))," _
                    & "$K2>DATE(YEAR(TODAY())-5,MONTH(TODAY())-4,DAY(TODAY())))),TRUE,FALSE)"
 
    '--------------------------------------------------------
    ' Follow up sheet
    '--------------------------------------------------------
    With shtFollowUp
        .Cells.Clear
        Set rngDest = .Range("A1")
    End With
 
    With shtSrc
        ' Temporary range for criteria
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(2, 1)
    End With
 
    ' Set Criteria
    rngCrit.Cells(1, 1).Value = "Date Criteria"
    rngCrit.Cells(2, 1).Value = strFormula

    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.ClearContents
 
    '--------------------------------------------------------
    ' Follow up Excluding Cash Sheet
    '--------------------------------------------------------
    With shtFollowNoCsh
        .Cells.Clear
        Set rngDest = .Range("A1")
    End With
 
    With shtSrc
        ' Temporary range for criteria
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(2, 2)
    End With
 
    ' Set Criteria
    rngCrit.Cells(1, 1).Value = "Date Criteria"
    rngCrit.Cells(2, 1).Value = strFormula
    rngCrit.Cells(1, 2).Value = "purchase type"
    rngCrit.Cells(2, 2).Value = "<>Cash"

    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.ClearContents
    Set rngDest = rngDest.CurrentRegion
    rngDest.Columns.AutoFit

   Application.ScreenUpdating = True
End Sub
Click to expand...

Hey Alex,

thank you for all your help and patience so far.

I just ran the VBA and it seems the code doesn't seem to be pulling the correct dates as it seems to be pulling recent dates, see sample below. - note I haven't the date :)

All Suburbs (version 2).xlsb.xlsm
K
1Decision date
230/06/2022
330/06/2022
429/06/2022
529/06/2022
629/06/2022
727/06/2022
823/06/2022
922/06/2022
1022/06/2022
1122/06/2022
1222/06/2022
1320/06/2022
1417/06/2022
1517/06/2022
1617/06/2022
1716/06/2022
1815/06/2022
1910/06/2022
209/06/2022
218/06/2022
228/06/2022
238/06/2022
248/06/2022
258/06/2022
267/06/2022
277/06/2022
283/06/2022
293/06/2022
3030/05/2022
3130/05/2022
3227/05/2022
3327/05/2022
3425/05/2022
3524/05/2022
3623/05/2022
3723/05/2022
3823/05/2022
3918/05/2022
4018/05/2022
4118/05/2022
4216/05/2022
4316/05/2022
4413/05/2022
4513/05/2022
4613/05/2022
4712/05/2022
4812/05/2022
4912/05/2022
5012/05/2022
5112/05/2022
5211/05/2022
5311/05/2022
Sheet10


Just confirming the code is for the following date ranges.

1 yearif todays date is between 8 months and 12 months from the purchase date
2 yearif todays date is between 20 months and 24 months from the purchase date
5 yearif todays date is between 56 months and 60 months from the purchase date

An example of this would be a 'beanie' is purchased on 10 September 2021, given the sales anniversary will occur in the next 4 months from todays date then it would be visible in the data set.

Once again thank you for your help thus far I really appreciate it.

Cheers,

Joach
 
Upvote 0
Can you try replacing the existing strFormula creation section with this one:
(I have used < and >, you will need to test whether you need to change either of those to <= and >=)

VBA Code: 
    strFormula = "=IF(OR("
    strFormula = strFormula _
                    & "AND(TODAY()<(DATE(YEAR(K2)+1,MONTH(K2),DAY(K2)))," _
                    & "TODAY()>(DATE(YEAR(K2)+1,MONTH(K2)-4,DAY(K2)))),"
    strFormula = strFormula _
                    & "AND(TODAY()<(DATE(YEAR(K2)+2,MONTH(K2),DAY(K2)))," _
                    & "TODAY()>(DATE(YEAR(K2)+2,MONTH(K2)-4,DAY(K2)))),"
    strFormula = strFormula _
                    & "AND(TODAY()<(DATE(YEAR(K2)+5,MONTH(K2),DAY(K2)))," _
                    & "TODAY()>(DATE(YEAR(K2)+5,MONTH(K2)-4,DAY(K2))))),"
    strFormula = strFormula _
                    & "TRUE,FALSE)"
 
Upvote 0
Alex Blakenburg said:
Can you try replacing the existing strFormula creation section with this one:
(I have used < and >, you will need to test whether you need to change either of those to <= and >=)

VBA Code: 
    strFormula = "=IF(OR("
    strFormula = strFormula _
                    & "AND(TODAY()<(DATE(YEAR(K2)+1,MONTH(K2),DAY(K2)))," _
                    & "TODAY()>(DATE(YEAR(K2)+1,MONTH(K2)-4,DAY(K2)))),"
    strFormula = strFormula _
                    & "AND(TODAY()<(DATE(YEAR(K2)+2,MONTH(K2),DAY(K2)))," _
                    & "TODAY()>(DATE(YEAR(K2)+2,MONTH(K2)-4,DAY(K2)))),"
    strFormula = strFormula _
                    & "AND(TODAY()<(DATE(YEAR(K2)+5,MONTH(K2),DAY(K2)))," _
                    & "TODAY()>(DATE(YEAR(K2)+5,MONTH(K2)-4,DAY(K2))))),"
    strFormula = strFormula _
                    & "TRUE,FALSE)"
Click to expand...
Alex you are an absolute legend, thank you for this!

I have amended the formula to include <= & => to that the end dates were included in the data set.

Do you know how i would conditional format the filtered data with a colour gradient to show the range of each anniversary

I thought it would be something similar to this but it isn't working:

1658740588438.png


If you could help me out that would be great.

Joach
 
Upvote 0
I don't think you will be able to do it that way.
What do the colours represent eg will it be the same colour for all years 1,2,5 and that the colour is the distance from the anniversay date ie 0-4 mths ?
Or is it difference colours for each of 1,2,5 1 colour for each ?
 
Upvote 0
Alex Blakenburg said:
I don't think you will be able to do it that way.
What do the colours represent eg will it be the same colour for all years 1,2,5 and that the colour is the distance from the anniversay date ie 0-4 mths ?
Or is it difference colours for each of 1,2,5 1 colour for each ?
Click to expand...
There would be a colour range (green, yellow, red) within each anniversary period (year 1, 2, 5). It is a visual cue to represent the upcoming deadline of the anniversary.

Green = 4 months until anniversary
Yellow = 2 months until anniversary
Red = Anniversary

This would then be set up for the 1, 2 & 5 year anniversary
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Advanced Filter using a date as the criteria
Replies
5
Views
330
Alex Blakenburg
A
L
  • Question
Use a Dynamic Range with Dates as a Criteria in FILTER Function
Replies
4
Views
705
Fluff
Fluff
W
  • Solved
HELP! SUMIFS/Logic/Dates Functions
2
Replies
10
Views
875
Alex Blakenburg
A
LRATOZ
  • Question
In Excel 2021 what is the formula to return a date when the max value happened (Compare the same month over many years)
Replies
2
Views
736
LRATOZ
LRATOZ
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
384
AndyEd
A

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

Share this page

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