Weeknum Feature

Jun 20, 2006

Does 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, 2010

I 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 :

View 4 Replies View Related

WeekNum

Nov 7, 2008

Formula 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?

View 9 Replies View Related

Using Weeknum With Countif Or Sumproduct

Feb 24, 2014

Essentially 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.

View 3 Replies View Related

Weeknum Says There Only 2 Days In Week

Jun 11, 2009

Why 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.)

View 9 Replies View Related

Weeknum Function Shows #Name

Jul 17, 2007

How 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.

View 3 Replies View Related

WEEKNUM Function Returns #NAME?

Jan 17, 2008

I 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...

View 6 Replies View Related

Weeknum (Based On Day Of Week And Time)

Apr 3, 2014

I 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

View 3 Replies View Related

Calculating Week Number Without WEEKNUM

Oct 14, 2008

We 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 Related

Get Weeknum To Recognize Change In Year Within Date

Dec 17, 2013

I 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

View 2 Replies View Related

Save Formulas With Weeknum Function ( With Workaround)

Apr 1, 2009

I 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.

View 5 Replies View Related

2007, Weeknum() Returning Incorrect Number

Aug 12, 2009

I 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 Related

How To Display Leading Zeros Using WEEKNUM Formula

Mar 14, 2012

In 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.

View 5 Replies View Related

Creating A Customized Accumulative Code Week (modified WEEKNUM) Formula

Jul 4, 2008

My 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)

View 9 Replies View Related

Date(Y,M,D) 'feature'

Nov 16, 2005

In 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?

View 10 Replies View Related

Way To Disable Help Feature

May 22, 2009

I just want to know if there is anyway to disable the help feature in Microsoft Excel?

I have to administer some exams.

View 9 Replies View Related

Autosum Feature In VBA

Jun 10, 2006

Here 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()

View 5 Replies View Related

Use The Linked Image Feature?

May 31, 2013

I 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 Related

Pivot Table Top Nth Feature

Sep 15, 2008

I 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.

View 9 Replies View Related

UNDO Feature To Work

Sep 13, 2007

I cannot get the UNDO feature to work.

View 9 Replies View Related

Removing Duplicates Feature Is Malfunctioning

Mar 13, 2009

i'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.

View 3 Replies View Related

Bypass Text Cells While Using Sum Feature

Dec 15, 2012

Is 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.

View 4 Replies View Related

Cancel Find Replace Feature

Nov 17, 2006

How do you cancel a find and replace once it kicks off.

Sometimes u make this mistake and have to click cancel 2000 times.

View 9 Replies View Related

Add Somekind Of Search Feature To Commandbutton(s)

Jul 13, 2007

How 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

View 9 Replies View Related

Turn Auto-Date Feature Off Possible

Aug 9, 2007

I 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?

View 9 Replies View Related

Camera Feature Possible Size Limitation

Nov 2, 2006

I 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 Related

Comment Feature Without The Red Triange On Cells With Comments

Jul 22, 2009

I'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 Related

Macro Including User Prompt Feature

Dec 18, 2009

Looking 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.

View 5 Replies View Related

Adding Browse Feature To Auto-prompt Box

Oct 15, 2007

I 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?

View 9 Replies View Related

Auto-complete Feature Cell Drop Down

Jun 1, 2007

I 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

View 4 Replies View Related







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