I have a list of random dates between 09/01/05 and 07/01/06. I would like to assign a week number to each date. For example, I would like it to be week 1 if the date is between the dates of 09/01/05 and 09/07/05. Is there any simple way of going about this?
View 3 RepliesWe have started a new venture from 5th April & I need to create a template for a year and assign week no against the dates. The week1 will start from 5th April & week 52 will end on 31st March.
Also there will be a summary of the Week No(Starting Date & End Date) against each week at the top of the template. I could put the formula for the summary part but I am unable to figure out how to assign week no against the dates as given below. It is very difficult to assign the week no manually for the whole year & also it is vulnerable to error. a formula across B7:B23 which can deliver the desired result?
Sheet2 Â ABC1Week NoStart DateEnd Date2Week15-Apr8-Apr3Week29-Apr15-Apr4Week316-Apr22-Apr5Â Â Â 6DateDesired
Result 75-AprWeek1 86-AprWeek1 97-AprWeek1 108-AprWeek1 119-AprWeek2 1210-AprWeek2 1311-AprWeek2 1412-
AprWeek2Â 1513-AprWeek2Â 1614-AprWeek2Â 1715-AprWeek2Â 1816-AprWeek3Â 1917-AprWeek3Â 2018-AprWeek3Â 2119-
[Code] .........
I need to use Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting-limit.htm" target="_blank">conditional formatting with more than 3 conditions. I have found a result for this when the formatting is being done to the cell containing the number but I need a different cell to be formatted. For example:
am pm
xx
xx
xx
xx
xx
xx
66
I need the cells marked by an x to go different colours depending on what number is in the final row of each column.
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
Total Work per day
Total Travel per day
Daily....................
I have a column where I am convering the Date into a Fiscal week number.
For example 10/6/2009 is Work week 41
Now I want to show October Week 41
I need to add the month and the text "Week" before the week number. what is the formula I use.
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.
I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.
------ UPDATE
In response to using CountIFS I have also tried-
=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.
I have a list of numbers such as:
A01
A23
A53
A64
A74
A128
B01... the goes on too approximatly D128
and i wanted to know if there is a way to assign the value to the row?
For example:
A23 in column A row 23
A53 in column A row 53
is there any method that achieves this?
I currently have a spreadsheet which I use to do an audit of the office stationery supplies.
There 3 columns at the focus of this question:
F - Number of items in stock
H - Stock re-ordering level
I - Order Required
I currently have an IF statement to tell me if I need to re-order any stock, by comparing the Number of items in stock against Stock Re-ordering level.
The statement is =IF(H1>=F1, "Yes","No")
This works fine under most circumstances. However, in some of the H cells, I have the text "Special" which indicates that the stock will only need re-ordering on a special occasion.
Any value I enter in F will cause the I to say "Yes".
Is there any way to make the word "Special" equivalent to 0, so I says "No"?
In column 'o' I have inserted a number for course name in column 'p'. I want to assign a course name to that particular number as follows:
Column 'o' Number ... Column 'p' Course name
1: A. B. C.
2: J. K. L.
3: P. Q. R.
4: X. Y. Z.
And so on as per course names.
If I enter number 1 in inputbox then I want to enter the course name in column 'b4' respectively.
I have in column A the frequency in weeks which I need an inspection to be done.
I have in Column B the last week in which the inspection was done.
I want to produce in column C the next week required to perform inspection.
So lets say:
A B C
26 28 54
If I add the 2 columns I get week 54. Is there any way I can convert that to read week 2?
I have a medication start end and admission date.
and i want to assign a number value of 1 to all the records that the medication start date is 2 days after the admission date. How do I do this using the ifs function.
I use my spreadsheet to keep score during tournaments. when i calculate my leaderboard at the end of the event i list the results first place all the way down until it finishes the list of players. i need to be able find players with the same or equal score and list them as tied for the same place.
another words if i get to the 3rd place and i had 3 players tie for third i need my leaderboard to show them all in 3rd place . currently it would number then 345. i listed below how it should format it. i also attached a file it will make it more clear.
I'm currrently using column I and col J
in col I are all the units of measurement: KG, PKT, BAG
in ColJ is the formula to return:
KG as 9999999
PKT as 8888888
BAg as 7777777
if(i2=kg, 999999999,"false!")
i was thinking of using if....but triple if formulas would be long isn't it?
PART2 of this would be using:
=IF((SUMPRODUCT(--(db!$A$2:$A$36000=$B$1),--(db!$F$2:$F$36000=C$5),--(db!$G$2:$G$36000=C$6),--(db!$E$2:$E$36000=$A7),--(db!$D$2:$D$36000=$B7),db!$I$2:$I$36000))=9999999,"KG","Invalid")
to check 5 values and if it is 999999999 it'll return KG
8888888 PKT
7777777 BAg
I need to count the number of times a value appears in a row and assign a number that tells which occurence of the text it is.
For example
Name Address
Jim 123 Smith Street
Jane 123 Smith Street
Bob 543 Apple Street
Mary 543 Apple Street
I would like a way to insert a column that puts a 1 next to Jim and a 2 next to Jane, a 1 next to Bob and a 2 next to Mary and so on.
Ultimately, I want to sort the list and delete all the twos, thus deleting the duplicate address entry.
I have found ways to count cells but what I am trying to do is in column F I have a list of meeting topics, and sometimes these repeat in a year. in my drop down menu I have all of them listed however my supervisor wants me to add a count after the meeting number in the 1_1X format where x is the number of times a topic has been used.
The output will be added to my macro here
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim y As String
Dim z As String
Dim b as Integer
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("MEETINGNUMBER")) Is Nothing Then Exit Sub
[Code] .......
I have 379 sample identities in my Excel spreadsheet in A2:A380. I want to randomly assign a number from 1-5 to each of these.
View 1 Replies View RelatedI have figured out how to assign a letter grade to a number, but am having trouble assigning it the other way, a number to a letter grade. For instance: If a student gets an A, I want the column next to it to indicate that the A represents a 4; a B represents a 3; a C represents a 2; D a 1; and F a 0. This will allow an easy grade point average calculation.
A 4 History
C 2 Math
A 4 English
B 3 Physical Ed
D 1 Science
GPA 2.80
I have a long list of items. I would like to assign the same number to each of the replicates. I've been trying to expand on this: = COUNTIF($A$1:A1,A1). which increments the number for each replicate, but don't seem to be getting anywhere. The attached file might make it clearer what I'm after.
View 3 Replies View RelatedI need to calculate and compare the extra number of hours worked less lunch time (0.5h per day) "per day sum by week" and total "per week" :
- number of hours above 40h per week;
- number of hours above 9h per day sum by week;
and keep the higher.
I've nearly found the formula to have extra hours for total week
=SUMPRODUCT(($B$3:$AK$3),--ISNUMBER($B$3:$AK$3),--(WEEKNUM($B$2:$AK$2+0,2)=AN1))-(SUMPRODUCT(--(WEEKNUM($B$2:$AK$2+0,2)=AN1),--ISNUMBER($B$3:$AK$3))*0.5)-40
But for total week I don't know how to sum per week hours above 9+0,5 per day
test extra hour.xlsx
How can i change week number to start from 1 if my year starts 1/07/2009 if i use function = weeknum() it returns the value 27.
View 7 Replies View Relatedas such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?
I am using the following formula, could someone explain this formula in
english?
=CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))
I want to use a formula to calculate what week number in the month it is (i.e 1 to 6) from a particular date.
I know how to calculate this on an annual basis (i.e. 1 to 52) but not within the month.
This is what I have so far...
=IF(OR(D58=1, D58>=D57), ROUNDUP(DAY(D61)/7,0),ROUNDUP(DAY(D61)/7,0)+1)
d58 is a Weeday formula looking at d61 which is the date i want to look at. d57 is the weekday number of the first day of the month in cell d61.
I am interested in modifying the below code so that instead of a text output of "Assignment A (20), Assignment B (5)" it would output only the sum of the numbers "25" for the corresponding week. I believe that this will be more clear with an example, so I'm attaching a small sample which shows what my current output is and what I'd like it to be.
Private Sub RestructureHrs()
'Start of Restructure
Dim l As Long ' loop through rows
Dim m As Long 'loop through rows to find last occurrance of person
Dim lLastRow As Long
Dim sPerson As String
Dim sOffice As String
Dim j As Long 'loop through person
Dim k As Long 'loop through weeks
Dim sOutput As String
Dim sOutputPerPerson As String
Dim lNextRow As Long
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
lNextRow = 2
For l = 2 To lLastRow
sPerson = Range("B" & l).Value
sOffice = Range("A" & l).Value
sOutputPerPerson = ""......................
I'm using excel 2003 and I searching for a small code to automaticly generate the begin- and end- date of a week (from monday till sunday) the only variable that I wanna give is the Weeknumber. So if I write a weeknumber in cell(a,1). I want the begin-date (monday) in cell (b,1) and I want the end-date (sunday) in cell (c,1).
for example:.....
Is there a function in Excel we can use identify a certain date to be the week no. ?
For example, 15 Feb 08 is (part of) Week 7 of 2008
Userform2 is popped up from another userform3.
How can I have userform 3 when clicked fill in a textbox on userform2 and then finally assign it to cell F3 on my worksheet.
VB:
Private Sub CommandButton2_Click()End Sub
So I need commandbutton2 (which needs to retain a value of 1` when clicked ) on Userform3 to assign the value of 1 to TextBox44 (Which is using control Source F3 so the value here ends up in F3
VB:
Private Sub TextBox44_Change()
If TextBox44 > 5 Then
MsgBox "Your entry must be part between 1 and 5", vbCritical
Exit Sub
End If
End Sub
I need to assign to a variable, the total number of text entries in col. A (or alternatively in a named range).
View 5 Replies View RelatedExcel 2003: I need code that, when an "x" is entered in a cell in the "Activity" worksheet to assign a temporary unit #, it will look for the next available Temporary Unit # in the "Assign" worksheet. Then mark that unit # as "assigned" (by placing an "X" in the column next to it) and copy it to a cell in the "Activity" sheet.
I will be doing the same thing with assigning different types of PO numbers. I figure if I have the code for the Unit #, I can use the same logic for the other assignments, with some modifications, of course.
I've attached a sample workbook.
If I am not considering the most effective way to accomplish what I am trying to do here, I have no ego at all about someone suggesting a better solution.
I am building a spreadsheet for contractors to use to submit home rehab bids. I am using a Windows 8 and Excel 2013. How to assign a monetary value to a number/quantity.
For example, when the contractor indicates 2 toilets need to be replaced and 1 toilet needs to be repaired, I would like to assign the value of $300 to replace a toilet and $150 to repair. Ultimately, I would like the 2 and the 1 to be automatically totaled to read as $750 in the total line at the bottom of the table.