Filter on X- days previous dates excluuding weekends

  • Hi,


    I need to add to my filter a greater than or equal to 3 days ago from today. I'm not sure how to do this to exclude weekends. If say if it's Monday or Tuesday it will need to go back 5 days (so weekends are excluded).


    I have posted what code i currently use but i can only get it to work for a Monday


    Thanks in advance


    Blunder



    Code
    Range("A1:o" & Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter Field:=8, Criteria1:=">=" & Format(Now() - IIf(Weekday(Now()) = 2, 5, 3), "mm/dd/yyyy")
  • Re: Filter on X- days previous dates excluuding weekends


    Hi Blunder1,
    Nice to meet you.
    I made a function.


    Regards, junho

  • Re: Filter on X- days previous dates excluuding weekends


    You might consider using a helper column to faciliate this, using a formula and filtering on TRUE values. See if you can adapt this.


    =AND(A1>=TODAY()-3,WEEKDAY(A1)<>1,WEEKDAY(A1)<>7)

  • Re: Filter on X- days previous dates excluuding weekends


    Nice to meet you too , Junho,

    This looks good, but how would i incorportate this into my filter?

    Thanks in Advance

    Blunder

  • Re: Filter on X- days previous dates excluuding weekends


    Hi Blunder,
    This works to me.


    Regards, junho

  • Re: Filter on X- days previous dates excluuding weekends


    Hi again, sorry to be a pain, but this doesn't seem to exclude the weekend when i run it. If i run the macro the date on the filter comes up as 12th Oct 2010 (that seems to be good). Then if i change the (Now(), 3 to (Now(), 5 the filter comes up as 10th where as it should be the 8th. As your function is way above my technical know how i'm not sure how to adjust it (i thought it may have somethng to do with the US and UK date format)!!

    Thanks again

    Blunder

  • Re: Filter on X- days previous dates excluuding weekends


    Sorry for late.
    Would you test this function?


    Regards, junho

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!