Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter for Day of the Week
I want to filter some data by, among other things, whether the date in one of
the columns is between the date the macro runs and Saturday of that week. The macro will not always be run on the same day of the week. I searched high and low on this board, but found nothing like that. I think what may work is using a dialog box, where the person running the macro would be asked how many days until Saturday. I would insert a column into the data that calculated the number of days between the current date and Saturday, and any rows where the number generated by the dialog box was less than or equal to the number showing in my calculated column would be filtered out. Does that sound like a reasonable solution? Any better ones? I don't know a thing about dialog boxes, but if it will work, I guess I'll do some self educating.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter for Day of the Week
If you doing an autofilter, then you can put in a custom criteria that the
data less than or eqaul to 1 date (next Saturday) and greater than or equal to another date(today's date). if you are in the US or Canada, this should be no problem. turn on the macro recorder and apply the dates manually to the filter. Now turn the macro recorder off and look at the recorded code. You can replace the hard coded dates recorded with your calculated dates (using the Weekday function) -- Regards, Tom Ogilvy "jmdaniel" wrote: I want to filter some data by, among other things, whether the date in one of the columns is between the date the macro runs and Saturday of that week. The macro will not always be run on the same day of the week. I searched high and low on this board, but found nothing like that. I think what may work is using a dialog box, where the person running the macro would be asked how many days until Saturday. I would insert a column into the data that calculated the number of days between the current date and Saturday, and any rows where the number generated by the dialog box was less than or equal to the number showing in my calculated column would be filtered out. Does that sound like a reasonable solution? Any better ones? I don't know a thing about dialog boxes, but if it will work, I guess I'll do some self educating.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter for Day of the Week
Unless I misunderstand your answer, I'm not sure how this will work when the
worker is running the macro on different days of the week. For instance, if the macro is hard-coded using Monday as the date the macro is being run, and the macro is actually run on Wednesday, woudln't the results of the macro be incorrect? "Tom Ogilvy" wrote: If you doing an autofilter, then you can put in a custom criteria that the data less than or eqaul to 1 date (next Saturday) and greater than or equal to another date(today's date). if you are in the US or Canada, this should be no problem. turn on the macro recorder and apply the dates manually to the filter. Now turn the macro recorder off and look at the recorded code. You can replace the hard coded dates recorded with your calculated dates (using the Weekday function) -- Regards, Tom Ogilvy "jmdaniel" wrote: I want to filter some data by, among other things, whether the date in one of the columns is between the date the macro runs and Saturday of that week. The macro will not always be run on the same day of the week. I searched high and low on this board, but found nothing like that. I think what may work is using a dialog box, where the person running the macro would be asked how many days until Saturday. I would insert a column into the data that calculated the number of days between the current date and Saturday, and any rows where the number generated by the dialog box was less than or equal to the number showing in my calculated column would be filtered out. Does that sound like a reasonable solution? Any better ones? I don't know a thing about dialog boxes, but if it will work, I guess I'll do some self educating.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter for Day of the Week
Well sure, but why would you hard code the date when you can use the Date
function to get today's date. That is why you use macros, - to make things dynamic - or at least one reason. On my test sheet, I had my dates in column 2 with headers in Row1 and staring in A1: Sub Macro1() ActiveSheet.AutoFilterMode = False Set rng = ActiveSheet.Range("A1").CurrentRegion s = rng(1).Offset(1, 1).NumberFormat sStart = Format(Date, s) sSaturday = Format(Date + 7 - Weekday(Date, 1), s) rng.AutoFilter Field:=2, Criteria1:="=" & sStart, _ Operator:=xlAnd, _ Criteria2:="<=" & sSaturday End Sub -- Regards, Tom Ogilvy "jmdaniel" wrote: Unless I misunderstand your answer, I'm not sure how this will work when the worker is running the macro on different days of the week. For instance, if the macro is hard-coded using Monday as the date the macro is being run, and the macro is actually run on Wednesday, woudln't the results of the macro be incorrect? "Tom Ogilvy" wrote: If you doing an autofilter, then you can put in a custom criteria that the data less than or eqaul to 1 date (next Saturday) and greater than or equal to another date(today's date). if you are in the US or Canada, this should be no problem. turn on the macro recorder and apply the dates manually to the filter. Now turn the macro recorder off and look at the recorded code. You can replace the hard coded dates recorded with your calculated dates (using the Weekday function) -- Regards, Tom Ogilvy "jmdaniel" wrote: I want to filter some data by, among other things, whether the date in one of the columns is between the date the macro runs and Saturday of that week. The macro will not always be run on the same day of the week. I searched high and low on this board, but found nothing like that. I think what may work is using a dialog box, where the person running the macro would be asked how many days until Saturday. I would insert a column into the data that calculated the number of days between the current date and Saturday, and any rows where the number generated by the dialog box was less than or equal to the number showing in my calculated column would be filtered out. Does that sound like a reasonable solution? Any better ones? I don't know a thing about dialog boxes, but if it will work, I guess I'll do some self educating.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter for Day of the Week
Very good! My data is actually in Column I, so just a couple of mods, and it
works like a charm: Sub Macro1() ActiveSheet.AutoFilterMode = False Set rng = ActiveSheet.Range("A1").CurrentRegion s = rng(1).Offset(1, 9).NumberFormat sStart = Format(Date, s) sSaturday = Format(Date + 7 - Weekday(Date, 1), s) rng.AutoFilter Field:=9, Criteria1:="=" & sStart, _ Operator:=xlAnd, _ Criteria2:="<=" & sSaturday End Sub Thank you very much! "Tom Ogilvy" wrote: Well sure, but why would you hard code the date when you can use the Date function to get today's date. That is why you use macros, - to make things dynamic - or at least one reason. On my test sheet, I had my dates in column 2 with headers in Row1 and staring in A1: Sub Macro1() ActiveSheet.AutoFilterMode = False Set rng = ActiveSheet.Range("A1").CurrentRegion s = rng(1).Offset(1, 1).NumberFormat sStart = Format(Date, s) sSaturday = Format(Date + 7 - Weekday(Date, 1), s) rng.AutoFilter Field:=2, Criteria1:="=" & sStart, _ Operator:=xlAnd, _ Criteria2:="<=" & sSaturday End Sub -- Regards, Tom Ogilvy "jmdaniel" wrote: Unless I misunderstand your answer, I'm not sure how this will work when the worker is running the macro on different days of the week. For instance, if the macro is hard-coded using Monday as the date the macro is being run, and the macro is actually run on Wednesday, woudln't the results of the macro be incorrect? "Tom Ogilvy" wrote: If you doing an autofilter, then you can put in a custom criteria that the data less than or eqaul to 1 date (next Saturday) and greater than or equal to another date(today's date). if you are in the US or Canada, this should be no problem. turn on the macro recorder and apply the dates manually to the filter. Now turn the macro recorder off and look at the recorded code. You can replace the hard coded dates recorded with your calculated dates (using the Weekday function) -- Regards, Tom Ogilvy "jmdaniel" wrote: I want to filter some data by, among other things, whether the date in one of the columns is between the date the macro runs and Saturday of that week. The macro will not always be run on the same day of the week. I searched high and low on this board, but found nothing like that. I think what may work is using a dialog box, where the person running the macro would be asked how many days until Saturday. I would insert a column into the data that calculated the number of days between the current date and Saturday, and any rows where the number generated by the dialog box was less than or equal to the number showing in my calculated column would be filtered out. Does that sound like a reasonable solution? Any better ones? I don't know a thing about dialog boxes, but if it will work, I guess I'll do some self educating.... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter for Day of the Week
Tom,
Can you help with one more thing? If I wanted to filter another file, same column, but this time just for rows that had today's date, how would I modify the macro? I tried to pull a few of the "Saturday" rows out, but that simply filtered all the rows... "Tom Ogilvy" wrote: Well sure, but why would you hard code the date when you can use the Date function to get today's date. That is why you use macros, - to make things dynamic - or at least one reason. On my test sheet, I had my dates in column 2 with headers in Row1 and staring in A1: Sub Macro1() ActiveSheet.AutoFilterMode = False Set rng = ActiveSheet.Range("A1").CurrentRegion s = rng(1).Offset(1, 1).NumberFormat sStart = Format(Date, s) sSaturday = Format(Date + 7 - Weekday(Date, 1), s) rng.AutoFilter Field:=2, Criteria1:="=" & sStart, _ Operator:=xlAnd, _ Criteria2:="<=" & sSaturday End Sub -- Regards, Tom Ogilvy "jmdaniel" wrote: Unless I misunderstand your answer, I'm not sure how this will work when the worker is running the macro on different days of the week. For instance, if the macro is hard-coded using Monday as the date the macro is being run, and the macro is actually run on Wednesday, woudln't the results of the macro be incorrect? "Tom Ogilvy" wrote: If you doing an autofilter, then you can put in a custom criteria that the data less than or eqaul to 1 date (next Saturday) and greater than or equal to another date(today's date). if you are in the US or Canada, this should be no problem. turn on the macro recorder and apply the dates manually to the filter. Now turn the macro recorder off and look at the recorded code. You can replace the hard coded dates recorded with your calculated dates (using the Weekday function) -- Regards, Tom Ogilvy "jmdaniel" wrote: I want to filter some data by, among other things, whether the date in one of the columns is between the date the macro runs and Saturday of that week. The macro will not always be run on the same day of the week. I searched high and low on this board, but found nothing like that. I think what may work is using a dialog box, where the person running the macro would be asked how many days until Saturday. I would insert a column into the data that calculated the number of days between the current date and Saturday, and any rows where the number generated by the dialog box was less than or equal to the number showing in my calculated column would be filtered out. Does that sound like a reasonable solution? Any better ones? I don't know a thing about dialog boxes, but if it will work, I guess I'll do some self educating.... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter for Day of the Week
Add a helper column with a formula of say
=AND(TODAY()<=C2,C2<=TODAY()+7-WEEKDAY(TODAY())) and then filter that column on a value of TRUE -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jmdaniel" wrote in message ... I want to filter some data by, among other things, whether the date in one of the columns is between the date the macro runs and Saturday of that week. The macro will not always be run on the same day of the week. I searched high and low on this board, but found nothing like that. I think what may work is using a dialog box, where the person running the macro would be asked how many days until Saturday. I would insert a column into the data that calculated the number of days between the current date and Saturday, and any rows where the number generated by the dialog box was less than or equal to the number showing in my calculated column would be filtered out. Does that sound like a reasonable solution? Any better ones? I don't know a thing about dialog boxes, but if it will work, I guess I'll do some self educating.... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter for Day of the Week
Bob,
This worked as well. I went with Tom's, so I didn't have to throw some steps in to copy that formula down, but this was a big help as well. Thanks, Jeff "Bob Phillips" wrote: Add a helper column with a formula of say =AND(TODAY()<=C2,C2<=TODAY()+7-WEEKDAY(TODAY())) and then filter that column on a value of TRUE -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jmdaniel" wrote in message ... I want to filter some data by, among other things, whether the date in one of the columns is between the date the macro runs and Saturday of that week. The macro will not always be run on the same day of the week. I searched high and low on this board, but found nothing like that. I think what may work is using a dialog box, where the person running the macro would be asked how many days until Saturday. I would insert a column into the data that calculated the number of days between the current date and Saturday, and any rows where the number generated by the dialog box was less than or equal to the number showing in my calculated column would be filtered out. Does that sound like a reasonable solution? Any better ones? I don't know a thing about dialog boxes, but if it will work, I guess I'll do some self educating.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
filter due this Week ,next week, nextweek | Excel Discussion (Misc queries) | |||
Auto calculate day of week to week of the year (not as serial) | Excel Discussion (Misc queries) | |||
How do I set up a week by week skill training schedule in excel? | Excel Discussion (Misc queries) | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) |