auto filter criteria (formula)

    I wish to try and autofilter my data using =mod(a1,7)


    A1 being a date.


    I have tried changing the date to show
    mmddyy ddd but it will not filter on the characters listed so even if it says 122203 Mon I can not filter using contains Mon in my filter.


    Long and short of it is I want to use auto filter to filter out all mondays, tuesdays, wednesdays...


    I could easily do it by adding data to my spread sheet and filtering on that but it just seems wrong to have to do so.

    I think you will need to use the advanced filter instead of autofilter. If your dates are in column A starting with the first date in A2, then somewhere else on the sheet put in some arbitrary heading other than what's in A1, and in the cell below it put the formula
    =MOD(A2,7)=1
    Then in the advanced filter, use these two cells as the criteria and it will show only the rows that have the function result true (Excel makes the necessary substitutions for the A2 reference). You could also use the weekday function instead of Mod.

Participate now!

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

Register Yourself Login