how to filter column by last working day in excel

BobBridges · #1 04-09-2016, 01:26 PM

It seems to me there's a function built into VBA that knows about working days. Let's see, here.... Yeah, here it is: WEEKDAY. You can get WEEKDAY to tell you what day of the week is the date you feed it; the return value is a number 1 through 7 where, by default, 1 is Sunday and 7 is Saturday.

On Sunday you want to you program to subtract 2 days from today's date, so that it can look at Friday; on Mon, subtract 3; on Tue through Sat, just 1. So here's one way to do it:

Code:

vd = Int(Now) 'today's date
vw = Weekday(vd) 'day of the week (1 through 7)
If vw > 2 then vw = 0
TargetDate = vd - vw - 2

I haven't tested this code, but if you work through it for Saturday (7), Sunday (1), Monday (2) and Tuesday (3) I think you'll see it works. There are other ways to do it, of course; this may be one of the simplest.

If your application needs to handle odd work days, you'd have to do it differently.

#2 04-09-2016, 02:49 PM

Thanks
will have to try on Monday
I work from Monday-Friday and I got it when I ran macro Tuesday -Friday (when I can filter by yesterday ) but Monday is the problem as my macro will look for yesterday but that is Sundays data not Friday

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Report chart filter not working	Asita	Project	5	07-07-2015 01:08 PM
Contact view filter not working	gebobs	Outlook	2	03-10-2014 06:28 AM
Range Name in Adv Filter -> NOt working	56_kruiser	Excel	2	11-20-2012 09:58 AM
drop down menu that control filter on other column	massi	Excel	0	08-24-2010 01:13 PM
junk email filter not working	michmurp	Outlook	0	09-15-2008 10:57 AM