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 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.
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.
what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?
For example (Sunday being the first day of the week):
January 5th 2008 = Week 1 of January
January 6th 2008 = Week 2 of January
February 2nd 2008 = Week 1 of February
February 3rd 2008 = Week 2 of February
WEEKNUM perfectly calculates this, but it is applicable for the whole year.
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'm using this formula from
[url]
Nth Day Of Week For A Month And Year
This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.
=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))
Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.
I would like to be able to change the year and month in A2 and B2 and have the calendar change.
I will be inserting rows between the weeks to return appts, if I can get this part working.
I could make a new tab for each month, but I thought I would give this a try....
This 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 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 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................
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....................
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 ###.
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.
Assuming all my date inputs are in column A, week in B, and month in C.
I found a formula from a forum in converting my date inputs to week number, and here is the formula in column B:
=IF(A40,"WEEK"&INT((A4+1-DATE(YEAR(A4),1,1))/7)+1,"")
- INT(bold) formula part, as those part I just copy paste it, add this and that, and it works
- the reason why I add the "+1" on the very last part is because on my weekly salary payment, every Saturday salary will be paid on the week after.
(ex: 04/28/12 is supposed to be WEEK17, but then I added +1 so it became WEEK18. The main reason is 04/28 is Saturday and like I said I need every Saturday salary to be paid on the week after)
So, I've got the solution to convert my date inputs to Week#, but then I also need this week# to be converted into "MONTH" in a way that the last day/s of any months, but still in the same week of the new month, to be grouped together.
- For an example, 04/28/12 is supposed to be considered as April month, but I need that day to be on the May month.
I have the following data by week:
------------------------------------------------------------------------
29-Mar 5-Apr 12-Apr 19-Apr 26-Apr 3-May 10-May
# of work days 4 4 4 4 5 5 5
Manpower 106 85 115 115 120 121 87
------------------------------------------------------------------------
On a second tab, I want to show the data by month:
----------------------------------------------------------------
Apr May Jun
# of workdays 21 (4+4+4+4+5) 21 20
Avg. manpower 108 104 ....
---------------------------------------------------------------
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 currently am trying to refine some spreadsheets at work (hospital setting). The type of files im working with are medication sheets where on the left it states the medication and to the right of it, the cells have the days of the month(1-31) but I need them to change depending on the day they come into our facility. Above the numbers i would also like it to say the day of week with the first initial (M, T, W, T, F, S, S) in the cells are the top. It is something that we have to make for each day it it gets really annoying and is a waste of time moving the dates over for every day. find a way where I can open the file and the numbers and letters are all in the right place without having to change it for the day that the patients are coming in.
View 3 Replies View RelatedI 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
I have 15 projects which are currently been taken, i need to record how much time each staff member takes each week/ per month on the projects, so at the end of week 5 i require a total column, and i require a totals columns at the end of the data.
How can i lay this out cos i'm having trouble visualing the data for per month and per week?
I have using the following Formula: =COUNTA(A3:A7,A10:A14,A17:A21,A24:A28,A31:A35)
Basically is counts how many week days there are in a particular month. Now I have a cell (B47) that counts how many Bank Holidays are in that month. I am having trouble using the formula then minus B47. I must be missing something really simple.
What I'm after is a sheet that self generates the day of the week in column A and the day of the month in column B. I have a month long sheet where daily entries get made in the DOW row, the day of the month is a reference. I have a macro to generate a new sheet for the next month and would like to auto populate the DOW and DOM. This typically gets done on the second day of the month (data from the first day is entered on the second)
View 4 Replies View RelatedIn 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
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 have been a long time lurker of MrExcel, but have always been able to find the answer to my question within the forums. However, I am stumped. I am trying to create a pivot table that will show incremental goals by week, when I have a set goal for the month already. However, I don't want to do a straight average across all the weeks in a month. I would like to see it continually grow during the month. How can i mathmatically do that in Excel?
For example:
Jim had 191 sales leads in the last week of the month in December. Of those 191, he closed 29 of them, for a 15.2% close rate. I want to see him raise that by 6% by next December, so (6%/12 (# months) = .5%). He will need to increase his close rate to 15.7% by the end of January. So, for the month of January, we expect 955 sales leads (5*191) and 149.935 (15.7% of 955) closed. However, I don't want to say Week 1, 2, 3, 4, and 5 in January should all have 29.987 closes, I would like to be able to say week 1 = 25, week 2 = 27 week 3 = 30 week 4 = 32 and week 5 = 34, but have that incremental increase to total 149.935.
There is (sadly) no changing the percents, or the closed leads number. These have already been submitted and aproved. I just have to be able to let people see the goals by week now, but if they want to see the monthly goal, it must equal what has been approved.
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 RelatedWhat I am trying to to is calculate the number of Years, Months, Weeks, and Days from one date to another. So far I can calculate years and months accuretly but I'm having trouble with the days and can't seem to figure out how to do the weeks.
I'm using the formula: =DATEDIF(B1,B2,"y")&" Year(s), "&MOD(DATEDIF(B1,B2,"m"),12)&" Month(s), and "&(MOD(DATEDIF(B1,B2,"d"),365))&" day(s)"
B1 is the current days date
B2 is the entered date
What I am getting when I enter the date 9/14/09 with the current date (8/3/06) is 3 years, 1 month and 43 days. when it shoud only be 3 years 1 month and 11 days. Any date I enter the days are not right. I can't seem to figure out what I am doing wrong. Also I'd like to get the weeks to come up also. Such as Start date 8/3/06 Entered date 10/19/09. What I want to see: 3 year(s), 2 month(s), 2 week(s), 2 day(s).
I have to download a report every week and have been manually checking the report weekly for changes.
View 5 Replies View RelatedI have a range of data which is as follows:
Week in month: 1 1 1 5
Site: 01/03 02/03 03/03 etc 30/03 etc
Leeds 10 9 15 20
Manchester 8 5 1 2
Etc
Here's what I need to produce:
March 08 April 08
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6
Leeds
Manchester
I need to sum week 1 to 6 for each month Mar, Apr and so on. The different sites are in the same order so that doesn't matter too much.
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.
Please see the attached xls file so see what I am referring to.
I have shipments that are going to different destinations (rotterdam, austria, london, etc.)
I would like to count the arrival dates in column H that fall under each week's span, but ONLY IF its corresponding value in column F is 'rotterdam'.
Column C contains the ideal numbers that I would like column B's formula to return. I plan on doing this for the entire year, but if someone can some up with a formula, I might be able to modify it for the rest.
Note: This is only an example spreadsheet, I am going to be referencing an external file with much more information on it.
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