Weeknum Feature
Jun 20, 2006Does VB have the WEEKNUM feature ?, I can use WEEKNUM in an Excel sheet ok, but it is not recognised in VB ... I have installed both the Analysis tool packs, the one for VB as well.
View 8 Replies
ADVERTISEMENT
Weeknum & VBA
Jan 5, 2010I got a file from my boss that is linked to an online table. This table contains customer contacts infos such as account number, activation date & time, etc, etc.. (1 contact = 1 line)
Now (for "quick filtering reasons" he said), my boss "needs" to have also in the sheet the week number for every contact we had..(as well as Month & Year)
and as the list grows up everyday with new entries, I thought I could use VBA.
so I wrote a little something that uses the date&time column to have the wanted infos added on every line :
WeekNum
Nov 7, 2008Formula below returns Year Week format.
e.g. 11/06/08 entered in cell C13 returns 0845
=VALUE(RIGHT(YEAR(C13),2)&TEXT(WEEKNUM(C13),"00"))
however entering 12/29/08 returns 0853, but management wants it as 0901.
01/05/09 returns 0902. Is this how Weeknum calculates, do I need to write my formula to if 53 then... or am I missing something with Weeknum?
Using Weeknum With Countif Or Sumproduct
Feb 24, 2014Essentially I have the following:
Sheet 1 (raw data)
column a = day
column b = weeknum
column c = Employee Name (5 names)
Sheet 2 (sheet I would like to create graphs)
I need to sum total records of each employee per week. For example:
column a2:a6 are each possible employee name
column b1:h1 are weeknums 1-7
I've tried countif but I'm only able to make that work if there's a helper column.
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.)
Weeknum Function Shows #Name
Jul 17, 2007How come the WEEKNUM formula in the attached file does not work?
The cell gives me "#Name?", and I have no idea why.
I noticed one thing though, which is that WEEKNUM function is described when I look in "Help", but the function is not in my "Insert Function"-wizard, so perhaps the function is not there at all, which is very odd.
WEEKNUM Function Returns #NAME?
Jan 17, 2008I am trying to get the week from a given data (from a cell that has been correctly formatted with date). =YEAR works, =MONTH works, =WEEKNUM does not work. I am using Excel 2003 and I have tried in both my workplace and at home...
I.e., I have a date in C2 (18-05-08), and =YEAR(C2) returns 2008, =MONTH(C2) returns 5, while =WEEKNUM(C2) returns #NAME, and =WEEKNUM(C2,2) does not work at all...
Weeknum (Based On Day Of Week And Time)
Apr 3, 2014I am currently using the =Weeknum(A1,15), so I have a Fri to Thu week. My issue is that I need anything that is done on Thursday after 3:00pm to be identified as the following week.
For example
04/03/2014 2:00pm = week 14
04/03/2014 3:01pm = week 15
Calculating Week Number Without WEEKNUM
Oct 14, 2008We have a fiscal calendar which starts Oct 1. I would like to display the proper week numbers. I worked out a formula which seems to work (except for week 53) but it would be better if I didn't have to rely on other users having the Analysis Toolpak installed. My date is located in '3930!I4' and this is the formula that works with the toolpak: ...
View 9 Replies View RelatedGet Weeknum To Recognize Change In Year Within Date
Dec 17, 2013I have a formula
=IF(I1239="","",IF(OR(H1239="Induction",H1239="One to One"),WEEKNUM(I1239)-WEEKNUM(K1239)+1,""))
Which works fine an counts the weeks from the input date I1239 from the initial start date K1239, however if the input date runs into next year 2014 the result ends up as a minus figure eg -47 instead of the next sequential figure which would be say 5 or 6
Save Formulas With Weeknum Function ( With Workaround)
Apr 1, 2009I have some formulas in my workbook that use the weeknum function from the analysis toolpak. I have the analysis toolpak installed, yet every time I save the file and reopen it, the formulas with weeknum are gone, with #N/A in its place as the formula. Sometimes the results of the formula from the last time remain (the cells contain the number of the week, but without the underlying formula), sometimes not. All the other formulas are fine. The formulas are simple, like =WEEKNUM(C2)
I have been trying to save as a 97-2003 .xls file. Today I'm trying to save as .xlsx, but I have to rebuild the formulas first, so I don't know how that's going to turn out.
2007, Weeknum() Returning Incorrect Number
Aug 12, 2009I need to use weeknum() for a number of situations in a project I'm working on. But when I use =WEEKNUM(DATE(2010;1;31)) it returns 6, instead of 4 as it should. Using it on today's date returns correct number.
View 3 Replies View RelatedHow To Display Leading Zeros Using WEEKNUM Formula
Mar 14, 2012In column A I have the following formula =CONCATENATE((YEAR(B1)), " - ", (WEEKNUM(B1,1)))
In column B I have a date.
I need column a to display in YYYY-MM format.
This works great from about mid-March thru the end of the year when the week number is 10 or greater. As an example when the date is 1/31/2012, column A displays as 2012 - 5. For sorting purposes, I need it to display as 2012 - 05.
Creating A Customized Accumulative Code Week (modified WEEKNUM) Formula
Jul 4, 2008My company uses a customized accumulative code week system, as in the year 2000 to today is about 442 weeks with the year 2008 starting around week 416.
Here is how my formula works(or how I would like it to work), if the date (in cell F8) is blank, nothing is displayed, if there is a date (format 2008-07-03) and it falls within 2008, WEEKNUM+416(416 is a fixed reference in J4), if the date falls within 2009, WEEKNUM+416(J4)+52, if the date falls within 2010, WEEKNUM+416(J4)+104. I only need it to go from 2008 to 2010.
This way J4 is a fixed code week reference where 416 is added to each week number, but it does not work for years 2009 and 2010 as it does not add the weeks from the starting reference.
Here is a copy of my formulas:
=IF(AND(F8)=0,""*(IF(AND(F8)
Date(Y,M,D) 'feature'
Nov 16, 2005In the Date function if you take 31/03/2007 and add 1 month to it:
=DATE(YEAR(ref),MONTH(ref)+1,DAY(ref))
you get 01/05/2007, which really isn't 1 month later. Bug or feature?
Way To Disable Help Feature
May 22, 2009I just want to know if there is anyway to disable the help feature in Microsoft Excel?
I have to administer some exams.
Autosum Feature In VBA
Jun 10, 2006Here is what I am looking for:
I have several groups of data separated by a couple of blank lines. For each group of data I would like to sum the rows in column F. I do not want to use the subtotal feature but rather the autosum feature for each section. It is important that the formula is actually entered so if I add additional rows of data the formula will keep calculating. So at the end of each data the formula is as follows =sum()
Use The Linked Image Feature?
May 31, 2013I want to use the "linked image" feature.This works well but when I don't know how to use it with a named cell (ie : "=$A$1:abcd" where abcd is a cell that I named).
View 2 Replies View RelatedPivot Table Top Nth Feature
Sep 15, 2008I am using Excel 2002 in Window XP. I have a spreadsheet that with a list of names, dates and scores. The names occur mutiple times. I have created a pivot table, however, I need to list the top 5 dates for each name with the associated score. The subtotal per name is the average of those scores.
My rows are Name and Date. Score is the column. When I use the Advanced Field options and activate the Top 10 options for the date field and change it to 5, the result is the top 5 scores instead of the top 5 dates with the associated scores.
UNDO Feature To Work
Sep 13, 2007I cannot get the UNDO feature to work.
Removing Duplicates Feature Is Malfunctioning
Mar 13, 2009i'm trying to remove duplicates from a worksheet containing customer contact info. the sheet has 9 columns with headings, and the duplicates appear in the last name and phone number column. (the sheet contains no outlines/groups/subtotals.)
i want to remove entries that contain the same last name AND phone number, however when i go to DATA>DATA TOOLS>REMOVE DUPLICATES, and specify the columns i want to remove duplicates from, it keeps deleting an entry that has the same last name, but not the same phone number.
i even tried removing duplicates from only the phone number column, and it still removes the phone number for the entry that has a duplicated last name, even though the phone numbers are different.
Bypass Text Cells While Using Sum Feature
Dec 15, 2012Is there a way to use the =SUM(--:--) feature, but to bypass any cells that contain text instead of numbers that are in the range?
I'm currently using this formula:
=SUM(N(I31)+N(L31)+N(O31)+N(R31)+N(U31)+N(X31)+N(AA31)+N(AD31))
...to only add up the number cells, as next to each cell, there is a column that has text (the reason for the "N(--:--)" is because depending on criteria, some of these cells print blank).
I just added 50 more columns that need to be added to the formula listed above and I will need to repeat this a dozen times. It will be much easier (and tidier) to be able to do something like this:
=SUM(N(I31:CL31)EXCLUDING CELLS THAT CONTAIN TEXT.
Cancel Find Replace Feature
Nov 17, 2006How do you cancel a find and replace once it kicks off.
Sometimes u make this mistake and have to click cancel 2000 times.
Add Somekind Of Search Feature To Commandbutton(s)
Jul 13, 2007How can I add somekind of (auto)search feature to a CommandButton?
So when I click CommandButtonA I get transfered to the first keyword (content) in Column A starting with the letter: A (or: a)
Private Sub CommandButtonA_Click()
' When you click on CommandButtonA goto firts keyword in Column A starting with A (or: a)
' When you click on CommandButtonB goto firts keyword in Column A starting with B (or: b)
' etc, etc, etc.
End Sub
Turn Auto-Date Feature Off Possible
Aug 9, 2007I am working w/ values that are interpreted as dates by Excel. The issue is that as I change it through code and have it displayed as I need it, I have to move it around to other locations w/in current sheet or to other sheets it gets reformatted to a date or serial number.
way to disable this feature as akin to turning off auto-correct feature. But have not found any method to do so. Can this be done in code?
Camera Feature Possible Size Limitation
Nov 2, 2006I want to use the Camera facility to show a 13 column by 19 row section of a large report in another WorkBook. I have outlined the area I need copied .... clicked the Camera icon .....but when I enter the data in the receiving sheet I keep getting the message ...." The Picture is too large and will be truncated" Is there a maximum size that can be transferred
View 2 Replies View RelatedComment Feature Without The Red Triange On Cells With Comments
Jul 22, 2009I'd like to know how to have the comment feature without the red triange on cells with comments. I didn't want to use data validation comments because I want the hover feature.
View 5 Replies View RelatedMacro Including User Prompt Feature
Dec 18, 2009Looking to write a macro which includes a prompt to the user for use in Excel 2007.
Specifics....Have a workbook including many worksheets with a common layout. Would like to write a macro to copy specific defined ranges from one worksheet to another but within the macro it would prompt the user to type in the worksheet name to copy the ranges from.
Example....User is at worksheet C in the file containing worksheets A, B, C, and D. User starts the macro and a prompt comes up for the user to input the worksheet name to source the ranges from (in this example A). User types in "A" in a dialog box and the macros copy/pastes the contents from range B4:B35 and Z2:z20 from worksheet A into those same ranges in worksheet C.
Adding Browse Feature To Auto-prompt Box
Oct 15, 2007I am using the following coding on my s/sheet at the moment...
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim r As Range
Application.EnableEvents = False
For Each r In Target
If Trim(Len(r.Value)) = 1 Then r.Value = UCase(r.Value)
Next
Application.EnableEvents = True
If Selection.Count = 1 Then
If Target.Row > 3 And Target.Column > 14 And Target.Column < 19 And Target.Value = "Y" Then
MY_LINK = InputBox("As you have entered 'Y' into this cell, please provide a link to the document. If there are no documents available for linking, please click cancel and change the value of the cell to 'N'.", "Document Link Request")
ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Address), Address:=MY_LINK
End If
End If
End Sub
The top code is to change all singular small letters to caps... and the second is to bring up an "enter hyperlink" box when "Y" is typed into a particular cell...
Can anyone edit this 2nd code to include a "browse" button, so that when entering the hyperlink, it can be to a file on the computer?
Auto-complete Feature Cell Drop Down
Jun 1, 2007I found this code for an auto-complete function from this website: [url]
It works well, but see the web example before using it. You have to name a range MyList with your potential values for it to work.
Currently, you have to click the drop-down arrows to see the potential entries. I want to make it so that after the user enters the first 3 characters it will automatically "drop-down" and show the potential entries. This will save a trip to the mouse and let the user see whether their entry is in the system.
Option Explicit
Dim i As Long
Sub KeyEventOn()
For i = 65 To 90
Application.OnKey "{" & i & "}", "'MyValidation """ & i & """'"
Next
End Sub
