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 RepliesI'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.
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 RelatedUsing 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
Spread1: =Sheet1!$E$2:$BE$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?
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?
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.
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.
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]....
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................
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
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 {
padding: 4px;
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 {
padding: 4px;
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.
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 :
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?
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 View RelatedEssentially 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.
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.)
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.
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...
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 +)
View 2 Replies View RelatedI 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
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.
View 7 Replies View RelatedIs 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.
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.
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.
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
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
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
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.
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.
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).