Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-08-2016, 05:43 PM
AC PORTA VIA
Guest
 
Posts: n/a
Default how to filter column by last working day in excel

trying to filter column by last working day
that being said if I set a code in macro to do that and I ran macro on Tuesday I will get data filtered for Monday (which would be easy just filter by yesterday but I would like to use same code to be able to ran macro on Monday and get data from Friday before
Reply With Quote
  #2  
Old 04-09-2016, 01:26 PM
BobBridges's Avatar
BobBridges BobBridges is offline how to filter column by last working day in excel Windows 7 64bit how to filter column by last working day in excel Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 04-09-2016, 02:49 PM
AC PORTA VIA
Guest
 
Posts: n/a
Default

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
Reply With Quote
  #4  
Old 04-09-2016, 08:34 PM
macropod's Avatar
macropod macropod is offline how to filter column by last working day in excel Windows 7 64bit how to filter column by last working day in excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Cross-posted at: http://www.mrexcel.com/forum/excel-q...day-excel.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-10-2016, 02:28 PM
AC PORTA VIA
Guest
 
Posts: n/a
Default

sorry about that
I am new to this
Reply With Quote
  #6  
Old 02-18-2017, 04:46 PM
AC PORTA VIA
Guest
 
Posts: n/a
Default

i have not figure this one out yet
can anybody help me please
all i am looking is a code in vba to filter column by date in the last used cell (column contains just dates) or filter by older date in that column
Reply With Quote
  #7  
Old 02-18-2017, 09:07 PM
BobBridges's Avatar
BobBridges BobBridges is offline how to filter column by last working day in excel Windows 7 64bit how to filter column by last working day in excel Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

So tell me what you tried regarding what I told you back in April, and what happened when you tried it.
Reply With Quote
  #8  
Old 02-19-2017, 10:44 AM
AC PORTA VIA
Guest
 
Posts: n/a
Default

no matter what i do with your code i don't get right date in filter
i work from Monday -Friday or sometimes Monday-Saturday
i would like to get code that I CAN RUN on Monday that would filter by Fridays date if Friday is the last working date for past week or Saturday if Saturday is the last working date for past week
but if i ran code on TUASDAY TO FRIDAY ,code would filter by one day before
in other words always filter by older date in that column (column A in my case)

Thanks for your help
Reply With Quote
  #9  
Old 02-19-2017, 03:26 PM
BobBridges's Avatar
BobBridges BobBridges is offline how to filter column by last working day in excel Windows 7 64bit how to filter column by last working day in excel Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Well, send me a copy of the program as you're running it now—you can just attach the .xlsm to this thread—and tell me what happens. I don't mean telling me "it doesn't work", I mean "the program stops at the 23rd line and displays the message '...' " or "when I start the program it refuses to run, highlighting the statement '...' " or something specific.
Reply With Quote
  #10  
Old 02-19-2017, 05:24 PM
AC PORTA VIA
Guest
 
Posts: n/a
Default

there is 2 macros in the ATTACHED FILE
macro 1 and macro 3

Thank you so much for your help
Attached Files
File Type: xlsm Book5.xlsm (18.5 KB, 17 views)
Reply With Quote
  #11  
Old 02-20-2017, 10:34 AM
NoSparks NoSparks is offline how to filter column by last working day in excel Windows 7 64bit how to filter column by last working day in excel Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

This doesn't use Filter, but, I think it might accomplish what you're after.
Code:
Sub Show_Last_Day_Only()
    Dim lr As Long
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Rows("3:" & lr - 1).Hidden = True
End With
End Sub
and to bring everything back
Code:
Sub Show_All_Days()
    Dim lr As Long
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Rows("3:" & lr - 1).Hidden = False
End With
End Sub
Reply With Quote
  #12  
Old 02-20-2017, 11:06 AM
AC PORTA VIA
Guest
 
Posts: n/a
Default

thank you so much for your help
this is great but I forgot to say that there is more then one row with same date
or it could be like this bellow in the same row-I will need all from same date to show
02/15/17
02/15/17
02/16/17
02/16/17
02/16/17
02/16/17
02/17/17
02/17/17
02/17/17
02/17/17
02/17/17
02/17/17
02/17/17
Reply With Quote
  #13  
Old 02-20-2017, 11:50 AM
NoSparks NoSparks is offline how to filter column by last working day in excel Windows 7 64bit how to filter column by last working day in excel Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Oh... now I understand why filter, try this
Code:
Sub Filter_For_Last_Date()
    Dim lr As Long
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("A2:A" & lr).AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:="=" & .Cells(lr, 1).Value
End With
End Sub
Reply With Quote
  #14  
Old 02-20-2017, 02:38 PM
AC PORTA VIA
Guest
 
Posts: n/a
Default

perfect
I had to change format on my cells for date but it looks like you got it on my test sheet
now when i try to do on my work sheet is not working since formatting is like this 1/17/2017 20:45:49 PM (and i cant change that)
do you know what needs to be changed (like I said i cant change formatting of the cells on the original sheet)
Thank you so so much
Reply With Quote
  #15  
Old 02-20-2017, 04:14 PM
NoSparks NoSparks is offline how to filter column by last working day in excel Windows 7 64bit how to filter column by last working day in excel Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

The best way to get a solution for what you're actually working with is to post a sample sheet with what you are actually working with.
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft