Using Days Of Week As Criteria For Filtering?
Oct 20, 2013Row H contains a date.
This date "example... 10/14/2013" Will always be a MONDAY
I am trying to figure out the code to filter out any Monday that is not of the current (changing) week.
ADVERTISEMENT
[VBA] Day Of Week Filtering
Sep 16, 2007I've been trying to write code to filter a list of date base on day of week (Mon, Tue, etc)
My problem is that i can't find a way to code the filter criteria... since the date does not contain any week of day constant. i don't want to create another column in my sheet to show the week of day constant and filter this instead.
Here's my not yet finished code...
Sub DayOfWeek()
Dim sAnswer As String
Dim bWkDay As Byte
Dim loMyData As Range
Dim iFilterCriteria As Integer
Dim Dateserial As Integer
Application.ScreenUpdating = False
sAnswer = InputBox("Please select day to display data (first 3 letter of the day, eg. mon)", Title:="Select day to display")
Auto Set Days Of Week
Oct 4, 2012Any 'date' or 'day' formula that will automatically insert days of the week in a column? I am attaching a sample.
I want to be able to use a drop-down menu to insert a day of the week (e.g.: Sunday) in cell B2 and have the succeeding days (Mon, Tues,Wed, Thurs, Fri, Sat) populate cells C2~H2 in sequence.....
Average Of First Three Days Of The Week?
Feb 9, 2014I have following data
Column A Name of the company
Column B Date
Column C Day
Column D Week of the Year
Column E High Price
Column F Low Price
My requirement is I need the average high price for the first three days of the week i.e Mon, Tue, Wed in Column I and average Low Price for the remaining days is Thu, Fri, Sat , Sun in Column J.
If in some case any day is missing then average high / Low price for the next two days is to be calculated.
Delete Days Of Week
Aug 16, 2013I had a column of text and numbers in col A; day of the week and date (taken from a web scrape, so have no control over how they are imported), such as in the following example. Additionally i have put a column of one's beside it in col B, and then summed them in col C.
A ..........................................................B .....................C
Friday 16/08/20131
Thursday 15/08/20131
Wednesday 14/08/20131
Tuesday 13/08/20131
Monday 12/08/20131
Sunday 11/08/20131
Saturday 10/08/20131
Friday 09/08/20131
Thursday 08/08/20131
Wednesday 07/08/20131
Tuesday 06/08/20131
Monday 05/08/20131
Sunday 04/08/20131
Saturday 03/08/20131sum=14
If i parse the text from the numbers, and then use a filter to remove Saturdays and Sundays, the sum total of col B will still remain the same. What i need is a method to totally delete or neglect Saturdays and Sundays so that the sum would equal 10 for example. Note that the data is updated daily, so in A1 today will be Friday 16/08/2013, but A1 tomorrow will be Saturday 17/08/2013. Also note that i have used 1's in my example, but that is just for explanation purposes, the actual data is varied.
I have just spent the last two days making this spreadsheet only to realize that weekend data is totally messing up my calculations.
Filter Days Of The Week
Oct 18, 2007How could I filter days of the week.
Although the date formatting contains the date, ie Thursday, 18 October 2007, when I try to use a contains filter it wont recognise the text of the day I want to filter.
Production Days In A Week
Sep 10, 2008I have a production schedule with start and finish dates for each project. To the right of this I have a matrix with "week starting" dates. What I am looking for is a formula that will indicate how many days in each week the project will take. Below are the cells filled in correctly. However, I did this manually. Any idea of a formula? I'm pretty new to this.
WEEK STARTINGSTARTFINISHUNITS PER DAY9/1/089/8/089/15/089/22/089/29/089/10/089/24/0813.4503530
Counting Days Of The Week
Jun 5, 2009I need to be able to count the number of each day that has passed so far this month. For example, so far this month the table would look like:
Mondays:1
Tuesdays:1
Wednesdays:1
Thursdays:1
Fridays:0
Saturdays:0
Sundays:0
I don't want the count to include the current day. My data is in a workbook called "Stock Today.xls" on a sheet called "Sales" in column S.
Weeknum Says There Only 2 Days In Week
Jun 11, 2009Why does Weeknum say there are ONLY 2 days in the first week of 2011? Aren't there 7 days in a week?
=Weeknum("1-1-2011",2) = Week 1
=Weeknum("1-2-2011",2) = Week 1
=Weeknum("1-3-2011",2) = Week 2
(I'm assuming the first day of the week is a Monday, but I still get wacky results if I start the weeks on a Sunday.)
Auto Fill Days Of The Week In A Row?
Dec 4, 2013I am trying to fill a row with abbreviated days of the week. example type Wed in cell A1 and cells B1 through Z1 change to the appropriate day Thu, Fri, Sat etc. I know how to do it with the fill off the menu but want to be able to change Wed to Thu and the same cells will change.
View 7 Replies View RelatedDays Of Month And Day Of Week Automatically Set?
Feb 25, 2013I currently am trying to refine some spreadsheets at work (hospital setting). The type of files im working with are medication sheets where on the left it states the medication and to the right of it, the cells have the days of the month(1-31) but I need them to change depending on the day they come into our facility. Above the numbers i would also like it to say the day of week with the first initial (M, T, W, T, F, S, S) in the cells are the top. It is something that we have to make for each day it it gets really annoying and is a waste of time moving the dates over for every day. find a way where I can open the file and the numbers and letters are all in the right place without having to change it for the day that the patients are coming in.
View 3 Replies View RelatedShow Days Of Week In Excel
May 24, 2013I have created a excel file which stores attendance for my employees.
The format is like
Select month in one cell
select year in other cell
And from cell e8 to ai8 i have nos (1-31) represent no of days in a month:
a8 - 1
b8 - 2
c8 - 3
d8 - 4 like wise till ai8
Now I want a calender where user need to select the YEAR, and then MONTH and based on the above selection both days as well as date (not the month and year) should be displayed.
I am currently using this formula "=IF(ISERROR(--(COLUMNS($E8:$E$8) & "-"&$C$2)),"",COLUMNS($E8:$E$8))" for date and your vb code to hide the cells depending on days of months.
I need something like this
sun mon tue wed thur fri sat sun mon
1 2 3 4 5 6 7 8 9
Number Of Days Remaining Until End Of Week?
Jul 31, 2013I am trying to get a formula that will give me the number of days remaining in the week. I have a formaula for number of days until the end of the month and number of days until the end of the quarter, but just need the week.
So if the formula was run today it would show 4 as the result.
Build Array With Days Of The Week
Aug 7, 2008This line will give me the current weeknumber
wkNumber = DatePart("ww", Now(), vbMonday, vbFirstFourDays)
But also i would like to have a dynamic array that will give me the dates of this week
Currently i do that using vlookup in a table stored in my personal.xls but that is no longer possible as i have to distribute my macro and i no longer have that possibility.
Calculate Days In A (06 Day)Working Week
Dec 27, 2008to calculate no.s of workdays between two given dates,
1.We work 06 days a week (so only Sunday is OFF)
2.What if I have some holiday between the given dates + Sunday OFF
List All Days For The Week For The Year
Oct 11, 2009i have the year in cell A1 (Just The Year)
what i was looking for is a formula that will list all the days of the week..
IE if i pick Monday form a drop down list than it would like all the mondays for all twelve (12) Months
so Jan Column would say
5
12
19
26
31
and so on for each month than if i picked a different day it would do the same
Using Microsoft Excel 2003 On Windows Xp Home Edition
Format Color For Some Days Of Week
Feb 16, 2007I have a Timesheet where I am trying to get Saturday and Sunday to auto fill with a certain color whenever the month is changed. The spreadsheet is setup so that if the month is changed in a specified cell, then the dates of that month auto fill down a specified column. I would like Saturday's and Sunday's to be a certain color without having to manually color fill them every time the month is changed.
Is there a formula or macro for this. I have tried setting up conditional formatting but I don't know how to get the color to follow Sat&Sun. I have attached the spreadsheet.
Format Date To Separate Different Days Of Week
Apr 29, 2014I am very new to Excel. The date is in column B as Tuesday, 29 April 2014, but I need to format the spreadsheet in such a way that every day of the week is a different colour e.g. Tuesdays are Blue, Wednesdays will automatically turn pink etc. how will I do that?
View 2 Replies View RelatedSetting A Formula To Relating To Days Of The Week
Oct 6, 2009Sheet1!A1 = Sat
Sheet2! A2 = I need this to equal Sun....
I tried =sheet1!A1+1 but got #value
COUNTA And Sum: Counts How Many Week Days There Are In A Particular Month
Feb 4, 2010I have using the following Formula: =COUNTA(A3:A7,A10:A14,A17:A21,A24:A28,A31:A35)
Basically is counts how many week days there are in a particular month. Now I have a cell (B47) that counts how many Bank Holidays are in that month. I am having trouble using the formula then minus B47. I must be missing something really simple.
Calculate Number Of Week And Days Between Two Dates?
Sep 28, 2011Any example of counting the # weeks/days between two dates?
View 1 Replies View RelatedYear,month,week,days Calculation
Aug 3, 2006What I am trying to to is calculate the number of Years, Months, Weeks, and Days from one date to another. So far I can calculate years and months accuretly but I'm having trouble with the days and can't seem to figure out how to do the weeks.
I'm using the formula: =DATEDIF(B1,B2,"y")&" Year(s), "&MOD(DATEDIF(B1,B2,"m"),12)&" Month(s), and "&(MOD(DATEDIF(B1,B2,"d"),365))&" day(s)"
B1 is the current days date
B2 is the entered date
What I am getting when I enter the date 9/14/09 with the current date (8/3/06) is 3 years, 1 month and 43 days. when it shoud only be 3 years 1 month and 11 days. Any date I enter the days are not right. I can't seem to figure out what I am doing wrong. Also I'd like to get the weeks to come up also. Such as Start date 8/3/06 Entered date 10/19/09. What I want to see: 3 year(s), 2 month(s), 2 week(s), 2 day(s).
Subtract Two Dates To Get Number Of Week Days - Networkdays Does Not Work
Dec 10, 2013I am trying to find a formula that will return the number of week days between two dates. My specific situation is that my job sets up work orders (WO) to be completed by our staff. We have 3 dates - the date the WO was created, the date the WO is due to be completed, and the date the WO was actually completed.
I would like to subtract the Complete date from the Due date. Generally, this should always equal zero because our staff should be completing WOs on the due date! But obviously that doesn't always happen. There are times that they complete them late, and times they complete them early (yay!).
The problem with NETWORKDAYS is that even when they are completed on time, the result is 1. This formula counts instead of subtracts. I adjusted the formula to =NETWORKDAYS(A3,A4)-1 which works fine for those WOs completed on time or completed late. But for those completed early, it adds (or subtracts, really) 2 days. So for a WO completed a day early, instead of it showing -1, it shows -3. I've attached an example of WOs and the NETWORKDAYS formula I've used so you can see.
Subtract Days.xls
I'm really looking for something that will subtract week days, not count them.
Calculate Numbers Of Days Elapsed A Week / Month Or 6 Months From Now
Dec 31, 2013In one column I'll have a list incrementing in 1w,2w,3w,1month and I want to be able to count the number of days that have elapsed till the latest cell. Right now I'm just winging it by saying there's always 31 days in one month using a COUNTA function, but I need it to be accurate.
View 9 Replies View RelatedGenerating List Of Week Working Days And Identifying Holidays
Oct 28, 2009From another thread I got this formula placed on B5:
=NETWORKDAYS(EOMONTH(B4,-1)+1,EOMONTH(B4,0),Holidays)
his generates the number of working days for that month (regardless of the date on B4) that exclude Holidays listed under the range "Holidays".
I also got this code (Credits to XXXX)
VBA - Finding And Counting Unique Dates And Days Of Week Within Date Range
Feb 11, 2013Within a user entered range of two dates, I would like to identify the individual calendar date(s) and count the number of Mondays which fall within the specified date range.I will eventually be using the same "Monday" code to find the same data for every day of the week within the dates ranges, but I figured I'd start with Mondays and build from there.
For Example: Date range 1/1/2013 - 1/15/2013 (date ranges could potentially encompass a full business quarter) Within the range, list each of the dates as dates. (used for comparative counting purposes elsewhere in the document)Count the number of Mons, Tues, Weds, Thurs, Fris, and Sats within the date range.Based on the example date ranges above; Mons = 2, Tues through Sats = 3 each.
VBA For Variable Filtering Criteria
Jan 15, 2014I have a data set in Excel that starts with row 13 and has 26 columns. The headers are in row 12.
A pop-up user form requires the user to select from a drop down menu i.e Top50, Top100, Top200...
Once this is selected, the script below applies a filter to the header row & filters Column O based on the Topxxx chosen in the user form.
The script then selects the visible cells in selected columns from the data set and copies these to another sheet.
The only thing that changes is the filter criteria i.e the user-defined choice of Topxxx from the user form.
Obviously I would like to try and avoid having to copy copious lines of coding for each filter criteria i.e Top50, Top100 etc etc.
Code:
If UserForm1.ComboBox2 = "Top50" Then
Sheets("MD14").Activate
A9 = ActiveSheet.Range("A9").Value 'Counta
ActiveSheet.Range("A12:Z12").Select
Selection.AutoFilter
[Code] ........
Filtering Via Multiple Criteria From Different Sheet?
Jul 30, 2014I have a workbook that has the main user interface on sheet3 and the main database i'm trying to filter on sheet2. Sheet3 has the button that executes the code to open the worksheet with the database and filter based on certain criteria and will filter unique id if a numeric value is entered and surname if text is entered.
Originally this worked ok when i had the button and the' rcrit' cell reference on the same worksheet as the database i'm trying to filter, but i decided to move them to the interface page as it was interfering with other things i'm trying to do.
Ever since then i can't seem to edit it to make it work from the other page! I've tried changing the filter ranges to reflect the new set up, but it seems to no longer be able to recognise numeric/none numeric values.
here's the code I'm using.
VB:
Sub filter()
Application.ScreenUpdating = False
Dim rCrit1 As Range
[Code]....
Filtering Checklist Based On Different Criteria
Aug 3, 2014There are 3 types of customers: A, B, and C. For different types of customers, there are different information needed to be filled in. For example, A customer need to fill in question 1,2,3 & 4 ; for B needs 1,2,3,4 &5; for C needs 1,2,3,4,5&6.
How can i:
1) create an advanced filter for selecting different types of customers
2) after selecting the customer type, the spreadsheet automatically generates the right questions?
Select The Filtering Criteria From A Drop Down Box
Nov 25, 2008One sheet will be where someone will be able to select the filtering criteria from a drop down box (ie. Industry, Company, Location, etc) then click an execute button.
Then the macro will go to the second sheet where all the data is, filter the data and create a new spreadsheet with the selected data.
I have been able to create a filtering macro on the data sheet but have not been able to place the macro on the first sheet and have the macro run correctly. I have attached the file.