Select Problem With Weekday Function

Jun 17, 2009

I'm having problems on something that should be easy. On the basis of a date, just move to a pre-defined day-of-the-week cell. If the day is a Monday, the active cell will end up being the range set aside for Mondays.

Sub findFirstDay()
Dim workDOW As Integer
workDOW = Weekday(Range("b1"))
Select Case workDOW
Case workDOW = 1: Application.Goto reference:="sunFirstday"
Case workDOW = 2: Application.Goto reference:="monFirstDay"
Case workDOW = 3: Application.Goto reference:="tueFirstday"
Case workDOW = 4: Application.Goto reference:="wedFirstDay"
Case workDOW = 5: Application.Goto reference:="thuFirstDay"
Case workDOW = 6: Application.Goto reference:="friFirstDay"
Case workDOW = 7: Application.Goto reference:="satFirstDay"
Case Else: Application.Goto reference:="wedFirstDay"
End Select
End Sub

The only case statement that executes is the "otherwise". I've tried working with WorkDOW as String, also to no avail.

View 3 Replies


ADVERTISEMENT

WEEKDAY Function

Apr 21, 2006

I have a running schedule with mutlile tasks scheduled for different day # or the same and would like to have the dates auto fill in once I enter the start
date.

example:
4/20/06 task 1 day 1
? task 2 day 2
? task 3 day 2
? task 4 day 3

Keeping in mind that I don't want to include weekends and that I'll need to delete a range of holiday dates.

View 10 Replies View Related

Weekday Function +1 Day

Sep 25, 2009

this formula in a cell. But I noticed it doesn't work if the day is friday because the next working day would be Monday.

I tried to replace the Today with weekday but I'm not doing something right.

View 2 Replies View Related

Use IF Function To Select Dates?

Dec 5, 2013

I want to make a calculation on a spreadsheet only if the date is a Friday. (weekly returns calculation) I can't seem to find out how to test the date to see if it is a Friday.

View 7 Replies View Related

Function-select Statement

Jul 21, 2008

I have this select statement:

Dim ResourceMonth As String
Dim mActiveSheet As String
ResourceMonth = Sheets("Finanace").Range("J2")
Select Case ResourceMonth
Case "Jan"
Sheets("Jan").Select
Cells(8, 4).Select
z = ActiveCell.Row
SSRR = ActiveCell.Value
mActiveSheet = "Jan"
Sheets(mActiveSheet).Range("[test1julycheckbook.xls]Jan!MaxHoursJan") = (Sheets("Workdays").Range("F2")) * 8

View 9 Replies View Related

Random Function On Select Range?

Mar 14, 2014

I would like to use the random function on a range of select numbers. I tried randbetween() but it won't work for me since my range of numbers are not in sequence (e.g. 1,2,4,6,7,8,9,10,21). Using randbetween() might result in numbers not within my range..

View 2 Replies View Related

Using Filter Function To Select More Than One Month

Feb 19, 2014

I currently have a column of data by month for the years 2013 to 2025. I want to show only dec, jan and feb of each year. I am currently using the filter function but I can only select one month at a time.

View 2 Replies View Related

Select Which Named Range To Use In A Function

Oct 7, 2008

is it possible to change the named range used in a formula based on the value selected in a dropdown. I have 4 named ranges NR1, NR2, NR3 and NR4. I would like to have a VLOOKUP that uses the named range that has been selected in a drop down. So if I change the drop down option I would get a different result as it will be looking at a different range.

It is not possible to put all of the values together as one named range as the intention is that I would select the range on a hidden and protected sheet so people would only see what they need to but I only have to manage one master sheet instead of having to create a new sheet for each range.

View 2 Replies View Related

Custom Function Select Case

Nov 5, 2012

I have a custom function that will tell me which quarter it is based on a date entered into a cell. It works except if the cell is empty is still returns the last Case but not my Case else. If the cell is blank I wanted the function to not return anything.

Function QuarterMonth(InputDate As Date)
Dim MonthNumber As Integer
MonthNumber = Month(InputDate)
Select Case MonthNumber
Case 1
QuarterMonth = "Q1 - 13"

[Code] ...........

View 9 Replies View Related

Transpose Function - How To SKIP / SELECT Certain Columns?

Feb 3, 2014

I am working on a =transpose function. Pretty simple right? Well now I am trying to have it SKIP/SELECT certain columns.

I have =transpose("date!B1:T1"), but now I want to "Select A,B,D,E,F"

=TRANSPOSE(INDIRECT("date!B1:T1"),"select A,B,D,E,F,G,H,P,Q,R,S,T")

View 1 Replies View Related

VB Code That Will Select All Cells To The Left In A Sum Function

Jul 2, 2008

I need to write some code that will write a sum formula and include all the cells to the left of the active cell, however this range is variable. I suspect it would be something like:

ActiveCell.FormulaR1C1 = "=SUM(xlLeft)"

but this just enters the formula "SUM(xlLeft)".

View 9 Replies View Related

Excel Function To Select Two Columns Based On Range?

Jul 30, 2014

Function to select two columns based on the header and the variable given.

Sample excel file attached for your ref.

Excel sample.JPG

Find the excel file in which I am looking for an formula which will look up variable in Col A for Eg USD and search the same in Row 1 and then will select COL D:E and so forth for other currency.

View 4 Replies View Related

Select Specific Month Of Many Years Of Data With Any Function?

Aug 14, 2012

Is there any way to select specify month of the many years of data with any function?

View 7 Replies View Related

Calculate A Certain Weekday

Oct 28, 2009

I need to set up something that "calculates" an orders next delivery date. We deliver on Tuesdays, Wednesdays, and Thursdays for different stores. IE.

Store 1 - Today's Date-Wednesday 10/28/09 = Next delivery date is Tuesday 11/3/09.
Store 2 - Today's Date-Wednesday 10/28/09 = Next delivery date is Wednesday 11/4/09.
Store 3 - Today's Date-Wednesday 10/28/09 = Next delivery date is Thursday 10/29/09.

View 3 Replies View Related

Rounding To A Particular Weekday

Nov 11, 2009

looking for for some help on a fairly simple problem: i've attached a worksheet, and in column B (Due to Supply Chain) i'd like to insert a formula that will subtract 21 days from the date in column Z (Pub Date), and then round that date to the nearest wednesday. is this possible?

fyi: the dates in column Z are in a yyyy-mm-dd format; they don't have to remain that way.

View 6 Replies View Related

If The Date Is A Weekday Then...

Feb 19, 2010

I'm trying to do an IF function involving the date. Basically if the current day is a weekday then I want the cell value to be 30. If the current date is a weekend then I want the cell value to be 50. I'm pretty lost on how to write the formula.

View 3 Replies View Related

Autofind First Weekday

Feb 22, 2010

Im working on a spreadsheet which has a column of dates formatted in "dd/mm/yy" there are always 365 days listed but these could be between any period. I am trying to create a table which looks up all mondays, tuesdays etc ....

I need a formula to find the first monday in the list and then to fill the rest of the tables. So far I manually input the monday and the rows below use =previous row + 7 to fill the following mondays, then in the next section it will refer to the monday and +1 to populate tuesday then the +7 in the rows below.

Ideally i would like a system which would log these dates automatically, the trouble is, sometimes the Thursday or another day will be first ( eg if the raw data is between 01/01/09 - 30/12/09 then the 01/01/09 is a thursday so the first thursday is 01 but the first monday will be 04/01/09.

View 5 Replies View Related

VBA Equivalent Of Weekday()

Dec 19, 2008

I'm trying to account for the date and have it change if the original falls on a weekend. I wrote it using the Weekday function, which I believe is a worksheet function and not a VBA one, as I keep getting a run-time error 5 (invalid procedure, call, or argument). Either that or I have something programmed wrong in it.

View 8 Replies View Related

Serial No With Weekday

Sep 20, 2007

I am receiving the data from the client , I want the serial no with the day of the date ... like this .. i ope u got it..

Like
thu01 20-Sep 2007
thu02 20-Sep 2007
thu03 20-Sep 2007
thu04 20-Sep 2007
Fri01 21 Sep 2007
Fri02 21 Sep 2007
Fri03 21 Sep 2007
Fri04 21 Sep 2007

Mon01 23 sep2007
mon02 23 sep 2007

I may receive more that 100 mails in a day .. so the serial should get updated of its own.

View 9 Replies View Related

Calculation Error Using WEEKDAY()

Sep 11, 2007

I want to calculate the number of weerks in a year. I use the following formula that seems to work

View 14 Replies View Related

Subtracting 1 Day When The Weekday Is A Thursday

May 28, 2009

Subtracting 1 day when the weekday is a Thursday. I need with the formula above (on AA2):

View 2 Replies View Related

Insert 4 Rows For Each Weekday

Apr 8, 2008

I have a worksheet that has in column A.

Mon
Tues
Wed
Thurs
Fri

I want to insert four new rows under each weekday.
Example:

Mon
blank row
blank row
blank row
blank row
Tues
blank row
blank row
blank row
blank row

I wish I had thought of this before I created 6000 rows consisting
of:
Mon
Tues
Wed
Thurs
Fri

Repeating over and over.

This was setup to track items ordered per day but I forgot I might have to order 4 items each day in some cases.

View 14 Replies View Related

Weekday - Calculate Date

Jan 29, 2008

Im trying to calculate a date in excel.

A2 = 28.01.2008
A1 should provide the date from A2 minus 1 weekday. (so 25.01.2008)

View 9 Replies View Related

Average By Weekday Of Month

Feb 9, 2009

I have a sheet that contains a number of documents entered into a system by a user.
Each month those number of documents are average by the number of working days in a month.

I'm using this formula, =AVERAGE(C4,22)

I would have to edit this for each month with the number of working days.

Is there a way I can have this formula automatically find the number of working days in a month specificed and average it out?

View 9 Replies View Related

Weekday Summary Table

Jan 14, 2010

I have a list of dates in Column A, with a list of associated values in Column B. I'd like to create a small summary table that will give a count of the dates in Column A and a sum of the values in Column B, broken into a bucket for each day of the week.

I know how to create this table using a short macro that would loop through the list of dates, but if possible I'd like to calculate the values using worksheet formulas so that
I don't have to run the macro each time another set of data is added to the list.

View 9 Replies View Related

Convert If & Weekday Formula To VBA

May 12, 2007

Formula in Range("E4")
=IF(WEEKDAY(E2)=1,"CN","T" & WEEKDAY(E2))

what this in VBA?

View 8 Replies View Related

Show Weekday Of Date

Jul 14, 2007

I am looking for excel to return a day from a date value, 14/07/07 = Saturday. Need this in VB ?

I have tried the DATE() and TODAY() etc.. Do i need to first tell excel a day by date so it can work it out, or can i do it in code???

The reason is im looking for it in VB to generate a report on a weeks data, which is inputted by a user. I will know the first date will be a range 1, but then need to convert it into a Day name ?

View 5 Replies View Related

Determine Weekday Of Date

Sep 19, 2007

The code is ok until I hit a month with 4weeks in it and days left over.
The code puts a week total in place of a Sunday, but as some months end before a Sunday appears the code just builds a Month end total sheet.

What I need in this case is a Week total even if there is no Sunday before it builds the Month end Total.

I hope i'm being clear

Sub NewSheets()
Dim Dte As Date, Dy As Date
Dim i As Long, j As Long, Dys As Long
Dim CountWeek As Boolean
Dim Shts As Long...

View 3 Replies View Related

Copy Every X Minutes Of Each Weekday

Apr 1, 2008

I have a cell that is set up as an external data source that is constantly changing 24 hours per day 5 days per week. What I am trying to do is copy that cell to another worksheet and save that dymanic data as a static value and save that data every 30 minutes synced to my PC clock.

View 9 Replies View Related

Date Of Last Weekday In Month

Jun 5, 2008

I used a formula I found on this site to find the last friday in a month. = DATE(" & Str(iYear) & ",1+1,0)+MOD(-WEEKDAY(DATE(" & Str(iYear) & ",1+1,0),2)-2,-7)

Is there an equivelant date function that can be used when coding in vb (not within a cell.) The VBA editor does not recognize this. I believe it is because date is reserved for variables. If anyone can make this line of code work in vb so as I dont have to asign it to a cell in a worksheet you would be my hero.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved