ISO Week Numbers
Feb 4, 2009
I am also using the following formula to return the date in Cell BE1:
=TODAY()
I am using the following formula to give me the ISO week number in Excel in Cell BE2:
=1+INT((BE1-DATE(YEAR(BE1+4-WEEKDAY(BE1,2)),1,5)+WEEKDAY(DATE(YEAR(BE1+4-WEEKDAY(BE1,2)),1,4),2))/7)
and the year in Cell BG2:
=YEAR(BE1)
I then use the following to give me the wk and year in this format "wk-yr", i.e "6-09" in Cell BF4.
=CONCATENATE(BE2,"-",RIGHT(BG2,2))
My question (finally) is that I am using this in a sheet that tracks issues that have deadlines. I manually enter in the dealine in the format "wk-yr". I have conditional formating that then colours a cell amber if it equals the current week and year, which is given in Cell BF4.
I want to use a traffic light system that will flag all issues in the weeks before the current week red, and the weeks after the current week in green, i.e. red = late!! I can't get my head round how I use a formual in teh conditional formating to correctly identify the relevent weeks? Any one any ideas? I could have the following situation for example (assuming that the current week is wk6 2009) and the formating should apply the colours as per my brackets:
10-08 (RED)
5-09 (RED)
6-09 (AMBER)
6-09 (AMBER)
7-09 (GREEN)
10-09 (GREEN)
6-09 (GREEN)
2-09 (RED)
View 9 Replies
ADVERTISEMENT
Feb 25, 2008
How can one determine a week number reliably with date as an input?
I can imagine that the function needs some kind of input as well, what is the first day of the year...
View 9 Replies
View Related
Jan 10, 2014
I wanted to work backwards from a weeknumber (as defined in Excel). In other words, given a week number (i.e. 5) what is the first date and last date of that weeknumber. These are the formulas that I came up with and they work fine.
Formula: [Code] ......
Ffor 1st of the week and
Formula: [Code] .......
For the last of the week.
Attached File : Weeknumber.xlsx‎
View 5 Replies
View Related
Apr 1, 2014
In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.
sortbyDates.xlsx‎
View 6 Replies
View Related
Feb 25, 2014
I have to examine a random number of cases every week. I used to have a little app that had a spot to ask me how many random numbers I need. So lets say this week I need to examine 15 cases, I'd enter in 15. Then it would ask me a range for the random numbers. So, lets say there are 250 cases, so I'd type in 250. Then it would spit 15 unique random numbers in sequential order. So, then I could look at me list of 250 case and review those 15 that the random number generator spit out.
Well, we just upgraded our computers to Win 7 and that little app does not work anymore. I've been playing around in Excel, and I've gotten pretty close to getting something thrown together, but I can't get it to work just right...
View 6 Replies
View Related
Jan 28, 2009
I require is a macro that will check the heading title of each weeks data e.g. WK01, WK02 etc, work out what week it is and input the week number in a new column (Week No) corresponding to that weeks data.
View 2 Replies
View Related
Feb 2, 2009
Every week I get sent a spreadsheet with the hours booked against specific codes, however, each weeks data is in a separate column (from weeks 1-52).
What I require is a macro that will check the heading title of each weeks data e.g. WK01, WK02 etc, work out what week it is and input the week number in a new column (Week No) corresponding to that weeks data and also total the time for that week and enter in the Total Hours column
Please see attached a sample spreadsheet for clarity.
Please also note that I have asked this question before (http://www.excelforum.com/excel-gene...o-columns.html) and DonkeyOte kindly supplied me with the code to work out the week number i.e.
=IF(COUNT($D2:$BC2),SUBSTITUTE(LOOKUP(9.99999999999999E+307,$D2:$BC2,$D$1:$BC$1),"Hrs WK",""),0)+0
however, as the weeks progress I have to manually copy the week number down and add the totals up so I believe a macro would be the best option as I am currently repeating the same task every week.
The macro needs to:-
(a) work out the week number and enter in ‘Week No’ column
(b) macro to copy each weeks data into Total Hours column
I would be most grateful for any assistance in this matter
View 8 Replies
View Related
Jan 19, 2007
I have a list of entrys (41 in total so far) from cell A1 down starting with the value 1. This entry represents the week ending 04/06/06. ie each entry represents a date for the week ending. So cell A2 will contain the no 2 and represent the week ending 11/06/06. Cell A3 will contain the no 3 and represent the week ending 18/06/06. It goes in order right down to number 41. I have a userform, what I want to do is have a combobox load all the entrys in as dates, not as the entry no. Then when I choose a week in the combobox it will load the appropriate entry no into another textbox? I dont want to add the dates to the spreadsheet.
View 4 Replies
View Related
Dec 31, 2013
In one column I'll have a list incrementing in 1w,2w,3w,1month and I want to be able to count the number of days that have elapsed till the latest cell. Right now I'm just winging it by saying there's always 31 days in one month using a COUNTA function, but I need it to be accurate.
View 9 Replies
View Related
Apr 7, 2014
I want to create a dynamic line graph using week and year numbers stated in another sheet.
e.g.
Start Year - 2012
End Year - 2014
Start Week - 3
End Week - 12
The top 2 rows above my graph data are as below:
Year - 2012 2012 2012
Week - 5 6 7 etc.
This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.
View 7 Replies
View Related
Jan 19, 2010
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
View 9 Replies
View Related
Dec 11, 2013
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
View 3 Replies
View Related
Aug 6, 2008
I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
View 9 Replies
View Related
Mar 6, 2010
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....................
View 9 Replies
View Related
Jun 16, 2014
I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
View 6 Replies
View Related
Feb 5, 2009
I have to download a report every week and have been manually checking the report weekly for changes.
View 5 Replies
View Related
Nov 24, 2009
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.
View 3 Replies
View Related
Oct 14, 2009
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
View 10 Replies
View Related
Apr 6, 2007
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)
View 9 Replies
View Related
Jun 21, 2006
How can i find the first day of a week
I have the week number and i need the date of monday of that week
Ex: Week number:24
First day of week(Monday):12-06-2006
View 5 Replies
View Related
Jun 27, 2006
I need the name of the day of the week from a date that has to be put together. This is going into a label on a UserForm.
I have a named range called wbDate. It is the month & year. The day of the month is the value of a TextBox (TBDay) on a UserForm. When I tried to combine these items, I used this
vToday = Month("wbDate") & " " & TBDay.Value & ", " & Year("wbDate")
vToday is dimmed as variant.
I am getting a type mismatch error and I'm not sure how to correct it.
What I am aiming for eventually, is the name of the day of the week.
View 4 Replies
View Related
Feb 11, 2014
In example file i wrote different dates along the column. How i can i write a filter in Table for showing only Thursdays from those dates?
File: [URL] .....
View 7 Replies
View Related
Oct 9, 2008
Does Excel know week numbers? e.g. Week 40 is Sunday 10/5/2008 to Saturday 10/11/2008. Here's what I have...
Column A Column G
date $ Amount
my data example...
10/5/2008 $10.00
10/5/2008 $20.00
10/8/2008 $12.00
10/11/2008 $8.00
10/12/2008 $25.00
10/13/2008 $75.00
What I want the formula to do is look in Column A find all of the dates that are in week # whatever and then look in column G and add all of those $ amounts. So the result would be...
Week 40 $50.00
Week 41 $100.00
View 3 Replies
View Related
Nov 2, 2008
1 2 3 4 5 6
11/1 11/2 11/3 11/4 11/5 11/6
sat sun mon tues wed thur
what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week
=$D$1
=$D$1+E5-1
=TEXT(WEEKDAY(D6),"ddd")
cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###.
View 3 Replies
View Related
Apr 6, 2009
I created a pivot table with daily dates listed from 1/1/2009- 3/31/2009. 3 months worth. I see i can group this data by hours, days, months, quarters and years but what i really need to do is group this by WEEK. Does anyone know a formula I could use to accomplish this?
View 3 Replies
View Related
Jun 5, 2009
Can somebody have a look at the workbook that I've attached.
I have set up a timeshhet calculator for work but the thing is, the hourly rates of pay are dependent upon the day of the week the Operative is working, and the time of the day.
We split the shifts AM & PM and the rates are decided as per the worksheet contained within the workbook "Rates".
The problem I am having is how to get the timesheet to understand the days.
For example, if an Op works the Sat night or Sun night, then the rate should reflect x1.5. If it's Sat/Sun daytime, then it's x1.25.
Again, and as per the "Rates" sheet (see top for the rate multiplier) Mon - Fri 09:00 - 17:00 is flat rate/standard hrs. If the work Mon - Fri evenings, then the rate is x1.25.
How can this be done?
View 10 Replies
View Related
Mar 17, 2009
I'm trying to set up a simple macro to loop through some number of dates on a sheet and blank out fields next to every instance of Saturday and Sunday. The only problem I'm having is actually finding the values "Saturday" and "Sunday"
Given a date in this format: 01/01/2009
I can convert to "Thursday"
Problem is, the vba code still "sees" 01/01/2009
I've tried copy / paste special / paste values to no avail.
I've tried changing it via VBA (a few different methods, including strings, formats, etc) with no luck. Even tried referencing my date cells from another spreadsheet and pasting the values back in. Can't seem to get 01/01/2009 to be "Thursday"
View 4 Replies
View Related
Aug 10, 2006
I normally use the WORKDAY function in order to create an involved scheduling
program. However, I have no idea how to create a formula that would count
Saturdays as a workday. I usually type in one date and then the dates kick
out to an end date several months later. How do I create a formula to
include Saturdays on a daily projection (e.g. A1=8/12/06, A2=8/14/06,
A3=8/15/06,etc...)?
View 9 Replies
View Related
Jan 26, 2014
Outlook allows you to easily specify, but eExcel always treats Sunday as the first day of the week by default. Yes you can manipulate that within formulas and functions, but if you use the autofilter in excel and choose "this week" (or "next week", or "last week") excel considers Sunday as the first day of the week. I would like to know how (or if) this can be changed to Monday as the first day of the week.
View 5 Replies
View Related
Sep 16, 2007
I've been trying to write code to filter a list of date base on day of week (Mon, Tue, etc)
My problem is that i can't find a way to code the filter criteria... since the date does not contain any week of day constant. i don't want to create another column in my sheet to show the week of day constant and filter this instead.
Here's my not yet finished code...
Sub DayOfWeek()
Dim sAnswer As String
Dim bWkDay As Byte
Dim loMyData As Range
Dim iFilterCriteria As Integer
Dim Dateserial As Integer
Application.ScreenUpdating = False
sAnswer = InputBox("Please select day to display data (first 3 letter of the day, eg. mon)", Title:="Select day to display")
View 9 Replies
View Related