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.
In the attch file i have the date coulumn from this date column i need to calulate the month & week no. (like WEEK1,WEEK2..)
The Week ( Monday to sunday) which need to be calculated is the week no. in the given month
like for month of April the week1 is print in the week column for 6april to 12 april date and Week2 print for 13 April to 19 april
I would like to calculate the week number of the month based on a date.
Now my days would only include working weeks (Monday - Friday).
Supposed the date is 12/31/2012:
M
31-Dec
T
1-Jan
W
2-Jan
TH
3-Jan
F
4-Jan
Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:
M
28-Jan
T
29-Jan
W
30-Jan
TH
31-Jan
F
1-Feb
It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:
M
29-Apr
T
30-Apr
W
1-May
TH
2-May
F
3-May
It will be considered as Week 1 of May since it occupies only 2 days of the working week.
Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.
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"......................................
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.
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 have week numbers from 1 to 52, now i want to get which week number will falls in which month, is there any formula in excel
for eg. Week 01 - 05 will fall in January month (2014), likewise..
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 uploaded an example file.
Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.
The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).
So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).
I have data for 6 monthsone column has all the dates
1/1/2013
1/2//2013
1/5/2013
1/8/2013
1/10/2013
1/15/2013
1/21/2013
1/31/2013
I have for 6 months like these dates
I need to convert all these dates to week 1- week 5
week 1: 1-7, week 2: 8-14, week3: 15-21, week4: 22-28, week5: 29-31
Instead of 1/1/2013 it should show week1 instead 1/10/2013 week2 like this
Is there a formula I can use to determine the number of work days in each week for a given month. The work week would start Sunday, with Friday and Saturday as days off. Each week would be in their own row.
i.e.
January
Week 1: 2 days
Week 2: 5 days
Week 3: 5 days
Week 4: 5 days
Week 5: 3 days
[Code] ..........
I'm attempting to force excel to auto update the day of the week, and the date in a spreadsheet. The date isn't as important, since it can be hard coded. The only problem there is some months have 31 days, some 30, and another with 28. I've uploaded an image of the spreadsheet, and you can see in field A1 the date/year is input. I'm wanting to find a way to force the days/dates in fields 2E and 3E to update based on the month.
View 9 Replies View RelatedThis might be a simple date transformation formula that I need
Column A has numbers like: 200517, 200530, 200544, 201036, 201043, etc
I'd like to get a formula in Col B in corresponding rows that would show date as dd/mm/yy
I know the numbers above are yyyyww = yyyy is year, and ww is the week of that year.
So the formula would have to convert that particular week to the day and month. I understand that are 7 days in a week, but if the formula would reflect Monday of that week, it would suffice.
I have to generate daily report in which data is fiscal week format. We need to show Fiscal week in actual month and day format. create a macro so that for every fiscal week anywhere in the sheet, it automatically converts fiscal week to month and days of the week
For example: WK 1 OCT FY2013 get converted to Oct 1-7
WK 2 OCT FY2013 coverted to Oct 8 - 14
WK 3 OCT FY2013 get converted to Oct 15 -21
WK 4 OCT FY2013 to Oct 22-28
WK 5 OCT FY2013 to Oct 29- Nov4
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
I've done a search on here to find out how to convert a date to a week number & found this: - =WEEKNUM(A1) which works fine, But I also want the result to display the year.
So 08/10/07 becomes WK40-07
I can't see how to do it!
just wondering if its possible to format a cell to display date and week number and if so how to go about it
eg
04/01/09 week 1
I have a spreadsheet that I use to convert a purchase order ship date from the actual date to the corresponding week it falls out on. The fiscal year always starts on February 1 regardless of the day of the week. The problem i am encountering is when the year changes. As soon as I enter 01/01/2010, the response I get is -4, where as 12/31/2009 is 48.
I am using the following formula that I found somewhere, where R2 = 02/01/2009 (02/01/2009 falls out on a Sunday). =INT((R2-DATE(YEAR(R2),2,1)-WEEKDAY(R2,1))/7)+2. I need to make the formula "not care about" the day of the week.
Of the 52 weeks in a year, I want to know the week number that a date falls in, e.g. date - 05/08/2013 falls in Week No. 32. What's the formula to get this answer?
View 2 Replies View Relatedon a macro i use to open, update a file and then save it in an archive.
Opening and updating the file is no problem, but i want to save under a dynamic name in a folder structure. This is a reoccurring task, and this way I update the same file each period but save a copy with the current data in an archive under a different name in the right place of a directory (archive).
My idea is to have a hidden cell in this workbook, where I can have the name calculated by simpe excel-formula, i.e. ="Filename_"&WEEK(TODAY())&"_"&YEAR(TODAY())&".xls".
The file should go automatically into an archive-directory, lets say C:data....archive2007 (2008, 2009, etc)
I want to add the last folder to my filename, so my macro knows the first part of the path and has to go look up the actual name it gets in order to be saved.
So I end up with a cell containting the filename: 2007Filename_35_2007.xls
Now I only need the macro which looks up this name, adds it to the hard programmed path:
C:data...archive
so that the file gets saved under as: C:data....archive2007Filename_35_2007.xls
this is how i start:
Workbooks.Open Filename:= _
"C:data....Filename.xls"
' here the file is updated...
Windows("Filename.xls").Activate
ActiveWorkbook.SaveAs Filename:= _
"C:data....archive'(how do i do this?)'", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
is it possible to display the week number of todays date (today()) from a physically entered start date (which would obviously be week one), the start date would be november 4th 2013.
View 3 Replies View RelatedI'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.
View 6 Replies View RelatedIs there a UDF that can determine the number of weeks for a date range specific that is not relative to the week number for the year but for the date range itself. i am aware of the weeknum function but this is for week number relative to the year. eg. date range 01/03/2008 - 31/05/2008 has approx 12 weeks and 14/05/2008 will be week number 10 for the range.
View 4 Replies View RelatedI have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.
Example:
1st Table
FROM TO WK
3/27/2009 4/2/200914
4/3/2009 4/9/200915
4/10/2009 4/16/200916
4/17/2009 4/23/200917
4/24/2009 4/30/200918
2nd Table
DATE
03/28/2009
04/11/2009
04/26/2009
Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.
I am putting together a simple table to display current week's data vs previous weeks. The current week's data is drawn from a status chart which changes frequently. The constant change is fine for 'Current' as I only want the current data displayed.
The problem I am having is calculating the number of late jobs that existed during the previous week.
The status log has a due date which is compared to the current date to determine 'on time' status for the current week.
Due dates are reissued regularly so I can't use
=COUNTIF(RANGE,WEEKNUM(NOW()-1)) to return data about last week from my status chart.
I have available a 'Movement Log' (in the workbook but a separate worksheet) which tracks the changes in the due date field, but I'm not sure how to integrate that data to calculate the # of jobs that were running late from the last week.
My thought is that I need to perform a count of the # of late based on a comparison of 'due date' to 'date of the last day of last week' with a way to insert the "old due date" from the movement log to replace what is shown in the status log if necessary.
Movement Log.JPG
Status Chart.JPG
I have the following data:
Column A = Date
Column B = Reservations made per day
For ex:
A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
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.
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 RelatedI 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................
