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

## VLOOKUP Returning Incorrect Value

Nov 28, 2012

I've created a spreadsheet to 'translate' number/letter combinations (sub-levels in the UK education system) into a numerical value. I looked up how to do this on google and, through a bit of trial and error, worked out that the lookuptable has to be in alphabetical order, etc.

My problem is when I enter in any of the 'P' values (see attached), it returns 0.5.

## 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: ...

## Excel 2007 :: Named Formula Incorrect Results

Sep 4, 2012

Using Excel 2007.

I setup a named formula: Spread1 =Sheet1!\$E\$2:\$BE\$2 where I entered % values to spread a an annualized figure over 53 weeks

On Sheet 2 I entered the amount to spread in \$D\$2 and in \$E.. I entered my formula: =Spread1 * \$D\$2. Everything appeared great!

But then I deleted a few columns on sheet2 so first calculation is now in \$G\$2

Calculations: Sheet2! \$G2:\$BG\$2 (the values in I,J,k while summing to the total are in the incorrect weeks based on the spread on Sheet1, BF and BG have #VALUE! errors)

Seems odd, if I highlight "Spread1" in the formula bar and [F9] I see an array of 53 possible values and the 3 values I expect are in the place in the array (elements 5,6,7).

How I can defeat it with the possibility that a user may delete a column?

## Excel 2007 :: Why Does Using Cell References In Formula Give Incorrect Value

Sep 30, 2011

Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:

Cell A1: 0.003529
Cell B1: 0.48

If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.

Why does using cell references in my formula give me the incorrect value?

## Serial Number Date Displaying Incorrect Day?

Dec 16, 2008

I maintain a class register in Excel to monitor student attendance. The first row shows the date of the class in the form dd-mm.

I need to identify all dates which fall on a Monday and thought that if I custom formatted a new row as "dddd" and enter the formula =DAY(cell ref) into the cells of this new row it would achieve this- I could easily spot the Mondays for the period under review.

What I'm finding, however, is that the formula seems to incorrectly state that 16th September 2008 is a Monday whereas it's actually a Tuesday- utterly bizarre!

I can get a fix simply by modifying the =DAY() formula by adding 1 to my formula [ie =DAY(A1)+1] but am wondering is this a "so called known issue" with Excel or has anyone else come across it? I have never previously come across this and consider myself to be an above average competency level user of the application.

## Incorrect Sumproduct Array: Sum Up The Total Number Of Hours Consumed On A Particular Activity Within A Specified Period

Oct 23, 2009

I am trying to sum up the total number of hours consumed on a particular activity within a specified period.

=SUMPRODUCT((\$C38:\$C176>=\$B7)*(\$C38:\$C176<=\$C7)*(\$B38:\$B176=\$D5))

Unfortunately, the formula above gives me the total number of activity and NOT the total number of hours within a specified period. I attached the file I'm working on as reference.

## Excel 2007 :: Returning Values Based On Criteria?

May 17, 2014

I'm using Excel 2007

I have the below data in sheet 1

In a separate column on sheet 2 i want to return the value of the data in ACC1 if the data in DEB/CRED is LC or SC and the value of the data in ACC2 if the data in DEB/CRED is SD

DEB/CRED
ACC1
ACC2

[code]....

## Application.FileSearch Not Returning Results On Some Pre 2007 Computers

Feb 11, 2008

Found code to find files and return results.. edited to my liking(barely.. i have no VBA knowledge).. it works on the PC I use. This is intended to be on a shared folder across a network for others to use, and it is not returning any results on the other PCs. This computer has Office 2003.. the rest have 2000. I can NOT figure out the problem. I have read in searches that Application.Filesearch is not reliable, but I do not know how to change it to any other type of coding
Attachment 26214

Option Explicit

Sub SrchForMSDS()
Dim i As Long, z As Long, Rw As Long
Dim ws As Worksheet
Dim y As Variant
Dim Fil As String, FPath As String
y = Application.InputBox("Search for file(s) named:", "MSDS Search")
If y = False And Not TypeName(y) = "String" Then Exit Sub
Application. ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "\My-SbscompanyMSDS Database"
' \#.#.#.#companyMSDS Databse or My-Sbs
.SearchSubFolders = True
.Filename = y................

## Excel 2007 :: VLookup Formula That Checks Cell Value Before Returning Value?

Dec 19, 2011

I am trying to find a formula for a vlookup that will check one cell for any value but if that cell has no entry then it will check a different cell to return the value how do i do this?

TEAMCSS Wetherill ParkCT NAME

above is where i need it to look CSS wetherill Park is cell C1 the cell that has no entry is H1, both of these cells have validation lists in them,

OS windows XP excel 2007

## Excel 2007 :: Lookup One Value And Returning Multiple Values (Horizontally) With Two Sheets

Jul 31, 2014

I'm basically working from 3 sheets for this so I'll start with an example of the data I'm using:

1st sheet:

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
margin: 3px;

[Code]....

I have 180 rows of data like this one in the first sheet

Second sheet(named sheet 1) is not used for this

Third sheet(named sheet 2):

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
margin: 3px;

[Code]....

Basically I'm trying to find column #3 value in my third sheet and return the second column value. Problem is that the data is located more than once in the third sheet so I need the value of each one of them. So, with this example, Q5942X is located twice in the third sheet and each time, it has a quantity of 2. I would need either to return the value 2 twice horizontally or even better, add the two together. The first sheet, the example is row #45.

This formula should be in the column following "majoration".

I am using excel 2007 and windows 7.

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

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

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

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

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

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

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

## Returning Positive Number Regardless

Jun 12, 2009

i have a list of numbers in column A and B and in column C is = B - A. But what i want is for Column C to always = positive number regardless even if say A = 10 and B = 8 the true answer is -2 but i would like the reading of 2. Is there a way to say B - A and if = - make it -( ) ? (otherwords +)

## 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

## Returning Number As Text In IF Formula

Oct 6, 2009

This is probably a pretty basic question, but I can't find an answer in Excel help or on the interwebs. I have an IF formula and I'd like 0 returned if the statement is false (e.g. =IF(A1<>0, A1, 0)); however, I would like the 0 returned as text. I've tried the TEXT function and using ` in different places, but all to no avail.

## Returning Nth Factor Of Number Via Formula?

Oct 15, 2013

Is there a way of returning the nth factor of a number via a formula?

That is to say, if I have 10, its divisors are 1, 2, 5 and 10, and if I wanted to return the second factor, it would be 2.

## 3D Formula Returning Zero - Need Lowest Number Above Zero

Aug 1, 2014

Code:
=IF(MIN(Sheet1:Sheet3!B1)=0,SMALL(Sheet1:Sheet3!B1,2),MIN(Sheet1:Sheet3!B1))

The above formula returns a zero value if there is a zero in more than one of the B1 cells in the worksheet range

I need the lowest number above zero in all the B1 cells across all worksheets in range.

## =sumproduct- Returning The Number Of Items

Feb 23, 2009

Im using the below formula -

=SUMPRODUCT(--(Jan!D9:D999 > 0)--(Jan!Y9:Y999="FALSE"))
But it keeps returning the number of items in JanD9:D999 > 0 , when i want the number of items in column Y which contain "FALSE" if D is greater than 0.

## Returning Number From Text String

Jul 24, 2007

I have been trying a few formulas to return numbers from a string of text. The problem I am having is that there are more than one set of numbers that I need returned.

Here is an example of the text entered in F2

"K2PT bus structure [2 locations] & strain structure South West of L9L12-12 [1 location] / Use #4/0, std, cu conductor, connector Cat ID#10842 at structure leg & connector Cat ID#9655 at #4/0, std, cu gnd loop to connect #4/0, std, cu gnd loop to str leg."

What I need returned in a cell H2 is the numbers beside the Cat ID#

10842, 9655

I tried a Return Right Value formula, but I think because there are more than one possible value it isn't working properly.

## Excel 2007 :: Macro For Finding Out Missing Number And Duplicate Number In A Range

Jan 22, 2013

Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1

## 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

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

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

## Selecting First Negative Number In Row And Returning Associated Column Value?

Feb 10, 2014

I have row of dates and underneath those dates I have a row of numbers. The numbers count down. For example:

[Code] .....

At the end of the row I want a cell to return the date that the row first started going negative at. In this example, it should display 10/31/2014 (for when it went to -60).