Weeknum() Question 


Weeknum() Question  Excel 
View Answers 
what happened to week 1?
A B C 1 Sunday Sunday 2 Week begin Date 27Dec09 03Jan10 3 Week number 53 2
Spreadsheet Formulas Cell Formula C3 =WEEKNUM(C2)
Excel tables to the web >> Excel Jeanie HTML 4
cell C3 should = 1 as it is the first Sunday in 2010
Martin
A B C 1 Sunday Sunday 2 Week begin Date 27Dec09 03Jan10 3 Week number 53 2
Spreadsheet Formulas Cell Formula C3 =WEEKNUM(C2)
Excel tables to the web >> Excel Jeanie HTML 4
cell C3 should = 1 as it is the first Sunday in 2010
Martin
Similar Excel Tutorials
Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
Helpful Excel Macros
WEEKNUM UDF for Excel Versions That Don't Contain This Function  UDF
 This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
 This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
Return the ISO Week Number from a Date in Excel  UDF
 Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
 Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Format Cells in The Long Date Number Format in Excel
 This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
 This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Hide Formulas in a Worksheet and Prevent Deletion
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Similar Topics
Why does Weeknum say there are ONLY 2 days in the first week of 2011? Aren't there 7 days in a week?
=Weeknum("112011",2) = Week 1
=Weeknum("122011",2) = Week 1
=Weeknum("132011",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.)
What's going on?
Lawrence
=Weeknum("112011",2) = Week 1
=Weeknum("122011",2) = Week 1
=Weeknum("132011",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.)
What's going on?
Lawrence
WEEKNUM starts a new week on Sunday. I need it to start on a Monday. Any ideas on how to adjust this? (I don't know VBA or other code...so I just need formulas).
Is there a better formula that below to solve this problem?
=IF(I39="Sun",WEEKNUM(H391),IF(F39
Is there a better formula that below to solve this problem?
=IF(I39="Sun",WEEKNUM(H391),IF(F39
I have been using Weeknum for some time.
The second parameter is to define whether the week starts on Monday(2)
or Sunday(1)
The first of the year 2006 was a Sunday.
Entering =weeknum(1/7/2006,2) which is a Saturday in cell A2 produced
1. And I agree.
Entering 1/7/2006 in cell A1 and weeknum(A1,2) in cell A2 produced 2
???.
Entering 12/25/2006 which is a Monday, in cell A1 and weeknum(A1,2) in
cell A2 produced 53 ???.
Entering =weeknum(12/25/2006,2) in cell A2 produced 1. That is weird.
You would expect 52.
Am I going to be obliged to use an If... to produce the expected
result ?
Can you give me some light ?
Thank you,
Wayne
The second parameter is to define whether the week starts on Monday(2)
or Sunday(1)
The first of the year 2006 was a Sunday.
Entering =weeknum(1/7/2006,2) which is a Saturday in cell A2 produced
1. And I agree.
Entering 1/7/2006 in cell A1 and weeknum(A1,2) in cell A2 produced 2
???.
Entering 12/25/2006 which is a Monday, in cell A1 and weeknum(A1,2) in
cell A2 produced 53 ???.
Entering =weeknum(12/25/2006,2) in cell A2 produced 1. That is weird.
You would expect 52.
Am I going to be obliged to use an If... to produce the expected
result ?
Can you give me some light ?
Thank you,
Wayne
Hello,
I'm trying to find a way to have a range of dates from Monday through Sunday display as the week ending date on Sunday.
For example  today is 11/29/2010, I can use WEEKNUM(A1,2) and get week 49 but I'd like that to show as 12/5/2010.
Thanks for your help!
Mike
I'm trying to find a way to have a range of dates from Monday through Sunday display as the week ending date on Sunday.
For example  today is 11/29/2010, I can use WEEKNUM(A1,2) and get week 49 but I'd like that to show as 12/5/2010.
Thanks for your help!
Mike
Hi Experts
How can i Divide my Monthly Target into Week Wise
But, For 1st Week Not to Include Sunday
For 2nd Week Not Include Saturday and Sunday
For 3rd and 4th Week Not Include Sunday
Below is the Example of my Data
How can i Calculate the Target of "AHMEDABAD"
Sheet3
* K L M 4 Campus FS Grand Total 5 AHMEDABAD 20 20 6 AURANGABAD 24 24 7 BANGALORE 132 132 8 BHUBNESHWAR 32 32 9 CHANDIGARH 36 36 10 CHENNAI 104 104 11 DELHI 176 176 12 GOA 32 32 13 HUBLI 24 24 14 HYDERABAD 84 84 15 INDORE 48 48 16 JAIPUR 52 52 17 JAMMU 20 20 18 KATHMANDU 20 20 19 KOLKATA 124 124 20 LUCKNOW 56 56 21 MUMBAI 224 224 22 NAGPUR 48 48 23 NASIK 24 24 24 NOIDA 76 76 25 PUNE 92 92 26 SURAT 0 0 27 TRIVANDRUM 16 16 28 VARANASI 28 28 29 VIJAYWADA 16 16 30 Grand Total 1508 1508 31 * * * 32 AHMEDABAD * 33 Weekly Target * 34 WEEK 1 * * * * * * * * * * * * * 35 WEEK 2 * * * * * * * * * * * * * 36 WEEK 3 * * * * * * * * * * * * * 37 WEEK 4 * * * * * * * * * * * * * 38 Total * * * * * * * * * * * * *
Spreadsheet Formulas Cell Formula L38 =SUM(L34:L37)
Excel tables to the web >> Excel Jeanie HTML 4
How can i Divide my Monthly Target into Week Wise
But, For 1st Week Not to Include Sunday
For 2nd Week Not Include Saturday and Sunday
For 3rd and 4th Week Not Include Sunday
Below is the Example of my Data
How can i Calculate the Target of "AHMEDABAD"
Sheet3
* K L M 4 Campus FS Grand Total 5 AHMEDABAD 20 20 6 AURANGABAD 24 24 7 BANGALORE 132 132 8 BHUBNESHWAR 32 32 9 CHANDIGARH 36 36 10 CHENNAI 104 104 11 DELHI 176 176 12 GOA 32 32 13 HUBLI 24 24 14 HYDERABAD 84 84 15 INDORE 48 48 16 JAIPUR 52 52 17 JAMMU 20 20 18 KATHMANDU 20 20 19 KOLKATA 124 124 20 LUCKNOW 56 56 21 MUMBAI 224 224 22 NAGPUR 48 48 23 NASIK 24 24 24 NOIDA 76 76 25 PUNE 92 92 26 SURAT 0 0 27 TRIVANDRUM 16 16 28 VARANASI 28 28 29 VIJAYWADA 16 16 30 Grand Total 1508 1508 31 * * * 32 AHMEDABAD * 33 Weekly Target * 34 WEEK 1 * * * * * * * * * * * * * 35 WEEK 2 * * * * * * * * * * * * * 36 WEEK 3 * * * * * * * * * * * * * 37 WEEK 4 * * * * * * * * * * * * * 38 Total * * * * * * * * * * * * *
Spreadsheet Formulas Cell Formula L38 =SUM(L34:L37)
Excel tables to the web >> Excel Jeanie HTML 4
I want to use the WEEKNUM function, and understand that you can choose for your week to start on a Sunday or Monday. But my week here at the office begins on a Saturday.
How can I get this formula to work for me?
How can I get this formula to work for me?
I have a spreadsheet that is used for a two week payperiod. The columns are Saturday to Sunday (ek), Saturday to Sunday (lr), then a column to total the hours in week 1 (sumek) and then another to sum the hours in week 2 (sumlr). When I enter the hours in the column for the sunday of the second week (column l) excel reformats the formula totalling the hours in week 1 to include the hours for the second Sunday (which should be only in week 2) so the formula becomes sum el.
I've gone into the advanced options in excel options and unchecked the "extend formulas" option and the same thing happens. Any suggestions?
Dear Excel gurus, 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.
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.
Hi all.
I'm running a spreadsheet that manages lessons in a certain course.
I have a cell that contains the date of the first day in the course, which changes, of course, from one course to another. Note that the course can begin in any day and not necessarily on the beginning of the week.
In the list of the lessons, each lesson has its date.
I'm looking for a formula that would give me the week number of the lesson, from the beginning of the course (column G in the attached file).
For example, if the course had started on Tuesday, September 18th, a lesson on Sunday, September 23rd would be on the second week of the course.
I cannot use the function "weeknum" because it is a part of the Analysis ToolPak addin which I cannot install.
Thanks a lot.
I'm running a spreadsheet that manages lessons in a certain course.
I have a cell that contains the date of the first day in the course, which changes, of course, from one course to another. Note that the course can begin in any day and not necessarily on the beginning of the week.
In the list of the lessons, each lesson has its date.
I'm looking for a formula that would give me the week number of the lesson, from the beginning of the course (column G in the attached file).
For example, if the course had started on Tuesday, September 18th, a lesson on Sunday, September 23rd would be on the second week of the course.
I cannot use the function "weeknum" because it is a part of the Analysis ToolPak addin which I cannot install.
Thanks a lot.
Hello everyone. I am using the WEEKNUM function to gather data from a running list. There are six columns which contain a quantity and then a date in the next column. My formula compare the week number of those dates against today's date. If they are the same week then the quantity is summed with the rest of the matches for that row. I the date isn't within the same week then the quantity for that date isn't added in. The problem I foresee is the last week of the year. I will have data that's in the 52nd week of 2008, and other data that's in the 1st week of 2009. When I get into the first week of January I will lose the data from December. Can anyone help think of a way to keep that data? Below is a sample formula. Thanks!
=IF((WEEKNUM(L3,1)=WEEKNUM(TODAY(),1))=TRUE,K3,0)+IF((WEEKNUM(N3,1)=WEEKNUM(TODAY(),1))=TRUE,M3,0)+IF((WEEKNUM(P3,1)=WEE KNUM(TODAY(),1))=TRUE,O3,0)
=IF((WEEKNUM(L3,1)=WEEKNUM(TODAY(),1))=TRUE,K3,0)+IF((WEEKNUM(N3,1)=WEEKNUM(TODAY(),1))=TRUE,M3,0)+IF((WEEKNUM(P3,1)=WEE KNUM(TODAY(),1))=TRUE,O3,0)
hi
I run a number of spreadsheet that use the '=weeknum(today(),2)' function in excel.
Now in the UK we are currently week 6 (forget the Sunday / Monday rule) but excel is returning week 7.
I am after a way to rectify this with out using a 1 solution that I will have to change next year.
Look forward to hearing from you
Thanks
I run a number of spreadsheet that use the '=weeknum(today(),2)' function in excel.
Now in the UK we are currently week 6 (forget the Sunday / Monday rule) but excel is returning week 7.
I am after a way to rectify this with out using a 1 solution that I will have to change next year.
Look forward to hearing from you
Thanks
I have a list of 200 records.
I want to count the number of records per week number.
I have tried
=SUMPRODUCT((WEEKNUM('Completed Rejects'!M$1:M$1000)=Analysis!L2))
Where WEEKNUM('Completed Rejects'!M$1:M$1000) is the list of dates
and Analysis!L2 is the week number (in a number format)
and I have tried
=COUNTIF(WEEKNUM('Completed Rejects'!M:M),Analysis!L2)
Sumproduct gives a #Value
Countif gives a #NUM!
Any help appreciated.
Analysis L M N 1 Week Count 2 22 #VALUE! #NUM! 3 23 4 24 5 25 6 26 7 27 8 28 Excel 2000
Worksheet Formulas Cell Formula M2 =SUMPRODUCT( ( WEEKNUM( 'Completed Rejects'!M$1:M$1000 )=Analysis!L2 ) ) N2 =COUNTIF( WEEKNUM( 'Completed Rejects'!M:M ),Analysis!L2 )
Completed Rejects L M N 1 Responsible Completion Date 2 Complete 12/07/2010 29 3 Complete 30/06/2010 27 4 Complete 16/07/2010 29 5 Complete 15/07/2010 29 Excel 2000
Worksheet Formulas Cell Formula N2 =WEEKNUM( M2 ) N3 =WEEKNUM( M3 ) N4 =WEEKNUM( M4 ) N5 =WEEKNUM( M5 )
^ these weeknum calculations aren't actually used and have to be deleted (I can't use a helper column in this case)
I want to count the number of records per week number.
I have tried
=SUMPRODUCT((WEEKNUM('Completed Rejects'!M$1:M$1000)=Analysis!L2))
Where WEEKNUM('Completed Rejects'!M$1:M$1000) is the list of dates
and Analysis!L2 is the week number (in a number format)
and I have tried
=COUNTIF(WEEKNUM('Completed Rejects'!M:M),Analysis!L2)
Sumproduct gives a #Value
Countif gives a #NUM!
Any help appreciated.
Analysis L M N 1 Week Count 2 22 #VALUE! #NUM! 3 23 4 24 5 25 6 26 7 27 8 28 Excel 2000
Worksheet Formulas Cell Formula M2 =SUMPRODUCT( ( WEEKNUM( 'Completed Rejects'!M$1:M$1000 )=Analysis!L2 ) ) N2 =COUNTIF( WEEKNUM( 'Completed Rejects'!M:M ),Analysis!L2 )
Completed Rejects L M N 1 Responsible Completion Date 2 Complete 12/07/2010 29 3 Complete 30/06/2010 27 4 Complete 16/07/2010 29 5 Complete 15/07/2010 29 Excel 2000
Worksheet Formulas Cell Formula N2 =WEEKNUM( M2 ) N3 =WEEKNUM( M3 ) N4 =WEEKNUM( M4 ) N5 =WEEKNUM( M5 )
^ these weeknum calculations aren't actually used and have to be deleted (I can't use a helper column in this case)
Hi All,
Happy Thanksgiving to all that it applies.
Happy ExThanks to all that it applies.
Now We Move Onward,
i have a list of dates in table 2(it does not matter the year) i just want it to refer to the month and day. so i want when the date found (2nd table) is the same (table 1) than i want colum Ai to say "No Work" (Example Not Shown)
AL 1214 i want all the dates in between (including the first and last date) to say "No Work" also the same with AL 1618 and if i change a date (2nd table) i want it to update in the first table
Sheet6
AF AH AI 2 11/23/2009 Monday Beginning of Week 3 11/24/2009 Tuesday Work Day 4 11/25/2009 Wednesday Middle Of Week 5 11/26/2009 Thursday Work Day 6 11/27/2009 Friday End Of Week / Pay Day! 7 11/28/2009 Saturday Day Off 8 11/29/2009 Sunday Day Off
Spreadsheet Formulas Cell Formula AH2 =TEXT(AF2,"DDDD") AI2 =IF(AH2="Monday","Beginning of Week",IF (AH2="Wednesday","Middle Of Week",IF (AH2="Friday","End Of Week / Pay Day!",IF (OR (AH2="Tuesday",AH2="Thursday") ,"Work Day",IF (OR (AH2="Saturday",AH2="Sunday") ,"Day Off","") ) ) ) )
Excel tables to the web >> Excel Jeanie HTML 4
Sheet6
AL AM 2 1/1/2009 39814 3 1/31/2009 39844 4 2/1/2009 39845 5 2/14/2009 39858 6 2/25/2009 39869 7 6/17/2009 39981 8 7/4/2009 39998 9 9/11/2009 40067 10 10/31/2009 40117 11
12 11/23/2009 40140 13 Through 14 11/27/2009 40144 15
16 12/23/2009 40170 17 Through 18 1/3/2010 40181
Spreadsheet Formulas Cell Formula AM2 =AL2
Excel tables to the web >> Excel Jeanie HTML 4
Column AM is formatted as "General" Please Ask Any unanswered questions!
Using Windows Xp Home Edition on Sp2 With Microsoft Excel 2003
Thanks!
Happy Thanksgiving to all that it applies.
Happy ExThanks to all that it applies.
Now We Move Onward,
i have a list of dates in table 2(it does not matter the year) i just want it to refer to the month and day. so i want when the date found (2nd table) is the same (table 1) than i want colum Ai to say "No Work" (Example Not Shown)
AL 1214 i want all the dates in between (including the first and last date) to say "No Work" also the same with AL 1618 and if i change a date (2nd table) i want it to update in the first table
Sheet6
AF AH AI 2 11/23/2009 Monday Beginning of Week 3 11/24/2009 Tuesday Work Day 4 11/25/2009 Wednesday Middle Of Week 5 11/26/2009 Thursday Work Day 6 11/27/2009 Friday End Of Week / Pay Day! 7 11/28/2009 Saturday Day Off 8 11/29/2009 Sunday Day Off
Spreadsheet Formulas Cell Formula AH2 =TEXT(AF2,"DDDD") AI2 =IF(AH2="Monday","Beginning of Week",IF (AH2="Wednesday","Middle Of Week",IF (AH2="Friday","End Of Week / Pay Day!",IF (OR (AH2="Tuesday",AH2="Thursday") ,"Work Day",IF (OR (AH2="Saturday",AH2="Sunday") ,"Day Off","") ) ) ) )
Excel tables to the web >> Excel Jeanie HTML 4
Sheet6
AL AM 2 1/1/2009 39814 3 1/31/2009 39844 4 2/1/2009 39845 5 2/14/2009 39858 6 2/25/2009 39869 7 6/17/2009 39981 8 7/4/2009 39998 9 9/11/2009 40067 10 10/31/2009 40117 11
12 11/23/2009 40140 13 Through 14 11/27/2009 40144 15
16 12/23/2009 40170 17 Through 18 1/3/2010 40181
Spreadsheet Formulas Cell Formula AM2 =AL2
Excel tables to the web >> Excel Jeanie HTML 4
Column AM is formatted as "General" Please Ask Any unanswered questions!
Using Windows Xp Home Edition on Sp2 With Microsoft Excel 2003
Thanks!
Hi,
I have read numerous postings on how to calculate week numbers in a year, but none seems to work correctly for what I need.
I need to calculate week numbers from years 2000 to 2030 so a lot of leap years are encompassed. The week runs Sunday to Saturday and Week 1 in any given year should start on the first Sunday of that year.
The closest formula I have found to what I need is:
=(A1WEEKDAY(A1)DATE(YEAR(A1+7WEEKDAY(A1)),1,1))/7+1
However, here are issues with the results:
01012000 (Sat)  Week 0 (correct)
02012000 (Sun)  Week 1 (correct)
31122000 (Sun)  Week 1 (incorrect, should be Week 0)
This should not be Week 1 as this date is still in 2000. This is a leap year and I am wondering whether this is part of the problem.
07012001 (Sun)  Week 2 (incorrect, should be Week 1)
This should be Week 1 as this is the first Sunday of the year 2001.
I don't mind if the Week 0 dates become Week 53/54, but I do need Week 1 to start on the first Sunday of each year.
Any help would be greatly appreciated!!
Thanks,
Jude
I have read numerous postings on how to calculate week numbers in a year, but none seems to work correctly for what I need.
I need to calculate week numbers from years 2000 to 2030 so a lot of leap years are encompassed. The week runs Sunday to Saturday and Week 1 in any given year should start on the first Sunday of that year.
The closest formula I have found to what I need is:
=(A1WEEKDAY(A1)DATE(YEAR(A1+7WEEKDAY(A1)),1,1))/7+1
However, here are issues with the results:
01012000 (Sat)  Week 0 (correct)
02012000 (Sun)  Week 1 (correct)
31122000 (Sun)  Week 1 (incorrect, should be Week 0)
This should not be Week 1 as this date is still in 2000. This is a leap year and I am wondering whether this is part of the problem.
07012001 (Sun)  Week 2 (incorrect, should be Week 1)
This should be Week 1 as this is the first Sunday of the year 2001.
I don't mind if the Week 0 dates become Week 53/54, but I do need Week 1 to start on the first Sunday of each year.
Any help would be greatly appreciated!!
Thanks,
Jude
Found this formula
INT((A1DATE(YEAR(EDATE(A1,3)),7,1))/7)+1 while searching this forum. http://www.mrexcel.com/board2/viewto...hlight=weeknum
Thanks to Barry Houdini for the same. My year starts from July 1 and the first week starts from the first Sunday. Above formula is giving the results but the problem is that the above formula treats Saturday as the first day instead of Sunday. Any help in modifying the above formula please?
Thanks
Chari
INT((A1DATE(YEAR(EDATE(A1,3)),7,1))/7)+1 while searching this forum. http://www.mrexcel.com/board2/viewto...hlight=weeknum
Thanks to Barry Houdini for the same. My year starts from July 1 and the first week starts from the first Sunday. Above formula is giving the results but the problem is that the above formula treats Saturday as the first day instead of Sunday. Any help in modifying the above formula please?
Thanks
Chari
I need help with a formula. I have a certain list of dates, by day...and I need them listed by "week ending" (where a week "ends" on a Sunday).
For example, for the week ending 5/23, I might have the dates:
5/23/10 (Sunday)  the formula should pull 5/23/10
5/22/10 (Saturday)  the formula should pull 5/23/10
And if the date was earlier...say 5/11/10...it should return "5/16/10" because that is the Sunday of the week that contained 5/11/10.
I know there is a way to do this with the Weekdays function combined in a formula, but I don't remember what it is. Does anyone know?
Thanks!
SundayST
For example, for the week ending 5/23, I might have the dates:
5/23/10 (Sunday)  the formula should pull 5/23/10
5/22/10 (Saturday)  the formula should pull 5/23/10
And if the date was earlier...say 5/11/10...it should return "5/16/10" because that is the Sunday of the week that contained 5/11/10.
I know there is a way to do this with the Weekdays function combined in a formula, but I don't remember what it is. Does anyone know?
Thanks!
SundayST
Hi
I need to display the week numbers of given dates, but want the weeks to run saturday through to friday. Weeknum only seems to work with weeks starting on sunday or monday rather than saturday. I am an excel beginner so please help!!
thanks
I need to display the week numbers of given dates, but want the weeks to run saturday through to friday. Weeknum only seems to work with weeks starting on sunday or monday rather than saturday. I am an excel beginner so please help!!
thanks
Hi All,
I have a cell that contains a date in the format dd/mm/yyyy
For the sake of this exercise, the week must begin on Monday.
I am trying to create a formula that can read this date and interpret which week within the month the date corresponds to. For example, 01/03/2010 would belong to week 1 of March.
There is however, a twist..!
The months must be broken down into 4 weeks, no more, no less! For this to work, the number of days in weeks 1 and 4 must be flexible. For this example, let us say that week 1 begins on a Tuesday (thus shortening the working week), Tuesday  Sunday must be added to the following week, therefore leaving you with week 1 spanning Tuesday 1st  Sunday 13th.
Week 4 must be able to expand and contract to suit the remaining days after week 3. If the month ends midweek, Wednesday 31st for example, week 4 must span Monday 28th  Wednesday 30th.
If there are more than 7 days left in month following the end of week 3, week 4 needs to be able to expand to accommodate all of these days. An example of this may be week 4 spanning Monday 23rd Tuesday 31st.
Any help would be greatly appreciated! VBA or formula based solutions welcome.
Cheers,
OG_Flynn
I have a cell that contains a date in the format dd/mm/yyyy
For the sake of this exercise, the week must begin on Monday.
I am trying to create a formula that can read this date and interpret which week within the month the date corresponds to. For example, 01/03/2010 would belong to week 1 of March.
There is however, a twist..!
The months must be broken down into 4 weeks, no more, no less! For this to work, the number of days in weeks 1 and 4 must be flexible. For this example, let us say that week 1 begins on a Tuesday (thus shortening the working week), Tuesday  Sunday must be added to the following week, therefore leaving you with week 1 spanning Tuesday 1st  Sunday 13th.
Week 4 must be able to expand and contract to suit the remaining days after week 3. If the month ends midweek, Wednesday 31st for example, week 4 must span Monday 28th  Wednesday 30th.
If there are more than 7 days left in month following the end of week 3, week 4 needs to be able to expand to accommodate all of these days. An example of this may be week 4 spanning Monday 23rd Tuesday 31st.
Any help would be greatly appreciated! VBA or formula based solutions welcome.
Cheers,
OG_Flynn
Hey All,
Sheet6
BH BI BJ 2 Monday 8 Tuesday 3 Tuesday 14 Tuesday 4 Wednesday 25 Sunday
Excel tables to the web >> Excel Jeanie HTML 4
i need help with a formula that will take the day of the week and add the number and give me the day of the week that many days later.
i want it to start counting the next day of the value in column BH
ive tried text(day(BH2),"DDDD")+BI2 i get a value error
Any Question Please Ask!
Thanks!
Sheet6
BH BI BJ 2 Monday 8 Tuesday 3 Tuesday 14 Tuesday 4 Wednesday 25 Sunday
Excel tables to the web >> Excel Jeanie HTML 4
i need help with a formula that will take the day of the week and add the number and give me the day of the week that many days later.
i want it to start counting the next day of the value in column BH
ive tried text(day(BH2),"DDDD")+BI2 i get a value error
Any Question Please Ask!
Thanks!
Hi,
I am running some tests on machinery and need to work out the number of hours the machinery has run per week. I need to do this on a Monday  Sunday based week. I can use the WEEKNUM function when the data fits neatly into a date range which is within one week and then SUMIF using the relevant weeknumber, but the problem I am having is when the test runs from say a Thursday to a Tuesday and spans two week numbers.
I have columns with 'start date/time' and 'stop date/time' in the format '01/12/2010 15:27'. Then a column which works out the total time run between the two dates.
I am running some tests on machinery and need to work out the number of hours the machinery has run per week. I need to do this on a Monday  Sunday based week. I can use the WEEKNUM function when the data fits neatly into a date range which is within one week and then SUMIF using the relevant weeknumber, but the problem I am having is when the test runs from say a Thursday to a Tuesday and spans two week numbers.
I have columns with 'start date/time' and 'stop date/time' in the format '01/12/2010 15:27'. Then a column which works out the total time run between the two dates.
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:
=IF(WEEKNUM('3930'!I4)>=40,WEEKNUM('3930'!I4)39,WEEKNUM('3930'!I4)+13)
I searched and found an alternate formula to replace WEEKNUM:
=INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1
but when I substitute it for WEEKNUM in my formula, it doesn't work:
=IF(INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)>=40,INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)40,INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)+14)
Returns the number 39723 rather than (week) 1
Can anybody help adjust my formula? Thanks
=IF(WEEKNUM('3930'!I4)>=40,WEEKNUM('3930'!I4)39,WEEKNUM('3930'!I4)+13)
I searched and found an alternate formula to replace WEEKNUM:
=INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1
but when I substitute it for WEEKNUM in my formula, it doesn't work:
=IF(INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)>=40,INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)40,INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)+14)
Returns the number 39723 rather than (week) 1
Can anybody help adjust my formula? Thanks
Hi All,
I pretty new to Excel and have been trying to solve this problem for days. I think I'm nearly there but am a bit unsure of the syntax I want to use.
Here is my problem:
I have a column of dates (1st column) and I want to do a VLOOKUP within an array ( E14:I24) to extract the whole row of any dates that fall into a particular week number.
****** httpequiv="ContentType" content="text/html; charset=utf8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9"> Deliverable Date (Planned)
Deliverable Date (Actual)
% Complete
Slippage (days)
16/01/2010
16/01/2010
100
0
So far I have achieved extraction of whole rows by specifying the date of the rows  see the following formula. However, this does not return all dates within a particular week number e.g all dates that fall into week 3:
{=VLOOKUP(DATEVALUE("16/01/2010"),E14:I24,{1,2,3,4},FALSE) }
I know how to give a cell (B11) todays date by:
=TODAY()
and another cell the week number by using the following formula that looks at the date in (B11):
=WEEKNUM(B11,2)
So I've tried to join these formulas together so it looks something like the following but obviously the syntax is incorrect:
{=VLOOKUP(WEEKNUM($E$14,1) = WEEKNUM($B$11,1),E14:H24,{1,2,3,4},FALSE)}
Can someone help me this please. I've exhausted my knowledge of Excel. Any help would be appreciated.
Many thanks
Shanz
I pretty new to Excel and have been trying to solve this problem for days. I think I'm nearly there but am a bit unsure of the syntax I want to use.
Here is my problem:
I have a column of dates (1st column) and I want to do a VLOOKUP within an array ( E14:I24) to extract the whole row of any dates that fall into a particular week number.
****** httpequiv="ContentType" content="text/html; charset=utf8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9"> Deliverable Date (Planned)
Deliverable Date (Actual)
% Complete
Slippage (days)
16/01/2010
16/01/2010
100
0
So far I have achieved extraction of whole rows by specifying the date of the rows  see the following formula. However, this does not return all dates within a particular week number e.g all dates that fall into week 3:
{=VLOOKUP(DATEVALUE("16/01/2010"),E14:I24,{1,2,3,4},FALSE) }
I know how to give a cell (B11) todays date by:
=TODAY()
and another cell the week number by using the following formula that looks at the date in (B11):
=WEEKNUM(B11,2)
So I've tried to join these formulas together so it looks something like the following but obviously the syntax is incorrect:
{=VLOOKUP(WEEKNUM($E$14,1) = WEEKNUM($B$11,1),E14:H24,{1,2,3,4},FALSE)}
Can someone help me this please. I've exhausted my knowledge of Excel. Any help would be appreciated.
Many thanks
Shanz
I've created a rudamentary function to calculate the start date of the week for the week number that is passed. Problem is, since this year (2009) started on a Thursday I need to subtract four days. Obviously this won't work come next year since it starts on a Friday, I was going to write this elaborate code to calculate the offset of Sunday from 1/1/yyyy but figured I should ask if anyone else had any better ideas.
do you?
Code:
do you?
Code:
Function WeekNumToDate(weekNum As Integer) WeekNumToDate = DateAdd("d", 7 * weekNum  4, "1/1/2009") End Function
Hey guys and gals,
I need some help on getting a week number calulated .. I've tried to use WEEKNUM function, but that didnt seem to work out right for me when I saved the file (based on computer system, it always changes
In my excel file (uploaded here on Mega.co.nz) I would like cell C3 to calculate what week it is bases on the contents of cell C9:C16 (or just on cell C9)
Would be cool also if I can just click on cell C3 and select a date (the sunday of that week) from a calendar function
Both ways I would like to see, so I can learn them myself and play around with them
P.s. i would like the file to print as you currently see it (so if anything else it added in a cell it wont print), and take note the date format is DD/MM/YYYY
Gracias
Call Data
A B C D E F G H I J K L M N O P Q R S T U V 2 Week 37 Year 2009 Report: Agent Group Performance by Period 3 Site ERA Communications Date range 07/09/2009  13/09/2009 05:45  16:00 4 Device: [iagroup01] 2216  2216 Created on: 20/01/2010 5 Created by: j.nicholls 6 Activity period ACD calls handled Non ACD calls handled Calls abandoned Calls outbound Calls requeued Calls transferred to agent Calls transferred from agent Conference calls Account codes ACD handling time (hh:mm:ss) Average ACD handling time (hh:mm:ss) Non ACD handling time (hh:mm:ss) Average non ACD handling time (hh:mm:ss) Outbound handling time (hh:mm:ss) Average outbound handling time (hh:mm:ss) Average manned agents 7 5:00 0 0 0 0 0 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0.0 8 6:00 0 0 0 1 0 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00 0:00:11 0:00:11 0.0 9 7:00 1 3 0 11 0 1 1 0 0 0:20:17 0:20:17 0:12:36 0:04:12 1:01:56 0:05:37 0.7 10 8:00 1 6 0 5 0 0 0 0 0 0:05:09 0:05:09 0:22:38 0:03:46 0:59:19 0:11:51 0.7 11 9:00 2 1 0 10 0 0 0 0 0 0:06:15 0:03:07 0:04:05 0:04:05 0:39:41 0:03:58 0.7 12 10:00 4 4 0 14 0 0 0 0 0 0:29:00 0:07:15 0:14:10 0:03:32 0:22:55 0:01:38 0.7 13 11:00 2 3 0 11 0 0 1 0 0 0:15:23 0:07:41 0:19:14 0:06:24 0:39:31 0:03:35 0.7 14 12:00 2 3 0 9 0 0 0 0 0 0:23:29 0:11:44 0:47:39 0:15:53 0:37:07 0:04:07 0.7 15 13:00 2 3 0 8 0 0 0 0 0 0:03:27 0:01:43 0:03:12 0:01:04 0:25:52 0:03:14 0.7 16 14:00 0 4 1 5 0 0 1 0 0 0:00:00 0:00:00 0:57:31 0:14:22 0:16:33 0:03:18 0.7 17 15:00 0 2 0 11 0 0 0 0 0 0:00:00 0:00:00 0:19:55 0:09:57 0:41:37 0:03:47 0.6 18 WeekNumYear Total Calls Inbound Outbound 19 372009 129 15 85 Total 14 29 1 85 0 1 3 0 0 1:43:00 0:07:21 3:21:00 0:06:56 5:44:42 0:04:03 0.6
Spreadsheet Formulas Cell Formula A2 ="Week "&WEEKNUM(LEFT(Q3,10),2)&" "&"Year "&YEAR(LEFT(Q3,10)) B19 =IF(Q3="","",WEEKNUM(LEFT(Q3,10))&YEAR(LEFT(Q3,10))) C19 =IF(SUM(G19:J19)=0,"",SUM(G19:J19)) D19 =IF(SUM(G19+I19)=0,"",SUM(G19+I19)) E19 =IF(J19="","",J19)
Excel tables to the web >> Excel Jeanie HTML 4
Call Data
A B C D E F G H I J K L M N O P Q R S T U V 20 21 Week 38 Year 2009 Report: Agent Group Performance by Period 22 Site ERA Communications Date range 14/09/2009  20/09/2009 06:00  16:00 23 Device: [iagroup01] 2216  2216 Created on: 21/01/2010 24 Created by: j.nicholls 25 Activity period ACD calls handled Non ACD calls handled Calls abandoned Calls outbound Calls requeued Calls transferred to agent Calls transferred from agent Conference calls Account codes ACD handling time (hh:mm:ss) Average ACD handling time (hh:mm:ss) Non ACD handling time (hh:mm:ss) Average non ACD handling time (hh:mm:ss) Outbound handling time (hh:mm:ss) Average outbound handling time (hh:mm:ss) Average manned agents 26 5:00 0 0 0 0 0 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0.0 27 6:00 0 0 0 0 0 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0.0 28 7:00 2 1 0 10 0 0 0 0 0 0:10:47 0:05:23 0:01:17 0:01:17 0:48:37 0:04:51 0.7 29 8:00 2 3 1 18 0 0 0 0 0 0:28:53 0:14:26 0:11:39 0:03:53 0:45:28 0:02:31 0.7 30 9:00 5 4 3 9 0 1 0 0 0 0:33:45 0:06:45 0:11:51 0:02:57 0:14:53 0:01:39 0.7 31 10:00 2 1 0 11 0 0 0 0 0 0:10:19 0:05:09 0:08:04 0:08:04 2:32:11 0:13:50 0.7 32 11:00 1 0 1 20 0 0 0 0 0 0:08:50 0:08:50 0:00:00 0:00:00 1:16:22 0:03:49 0.7 33 12:00 1 2 1 6 0 0 0 0 0 0:04:21 0:04:21 0:07:24 0:03:42 0:24:34 0:04:05 0.7 34 13:00 3 3 2 7 0 0 0 0 0 0:34:34 0:11:31 0:13:26 0:04:28 0:16:15 0:02:19 0.7 35 14:00 0 4 1 18 0 0 1 1 0 0:00:00 0:00:00 0:12:41 0:03:10 1:05:32 0:03:38 0.7 36 15:00 0 1 0 10 0 0 0 0 0 0:00:00 0:00:00 0:01:46 0:01:46 0:31:26 0:03:08 0.6 37 WeekNumYear Total Calls Inbound Outbound 38 382009 153 25 109 Total 16 19 9 109 0 1 1 1 0 2:11:29 0:08:13 1:08:08 0:03:35 7:55:18 0:04:22 0.6
Spreadsheet Formulas Cell Formula A21 =IF(A2="","",IF(AND(Q22="",A2="New Data Here"),"",IF(AND(Q22="",LEFT(A2,1)="W"),"New Data Here","Week "&WEEKNUM(LEFT(Q22,10),2)&" "&"Year "&YEAR(LEFT(Q22,10))))) B38 =IF(Q22="","",WEEKNUM(LEFT(Q22,10))&YEAR(LEFT(Q22,10))) C38 =IF(SUM(G38:J38)=0,"",SUM(G38:J38)) D38 =IF(SUM(G38+I38)=0,"",SUM(G38+I38)) E38 =IF(J38="","",J38)
Excel tables to the web >> Excel Jeanie HTML 4
The Two Above tables show how the data is formatted, Columns A through E have been added by me, F through V are an extract from our phone reporting software.
The below is the summary sheet were I am trying to collate the date
Summary
B C D E F G H I J 3 Week Year WeekNumYear Total Jobs Total Calls Total Inbound Total Out Bound 4 7/09/2009 Week 37 Year 2009 37 2009 372009 0 5 14/09/2009 Week 38 Year 2009 38 2009 382009 2 #N/A #N/A #N/A 6 21/09/2009 Week 39 Year 2009 39 2009 392009 5 7 28/09/2009 Week 40 Year 2009 40 2009 402009 3 8 5/10/2009 Week 41 Year 2009 41 2009 412009 11
Spreadsheet Formulas Cell Formula C4 ="Week "&WEEKNUM(B4,2)&" Year "&YEAR(B4) D4 =WEEKNUM(B4,2) E4 =YEAR(B4) F4 =WEEKNUM(B4,2)&YEAR(B4) G4 =COUNTIF('Job Data'!G:G,Summary!F4) B5 =B4+7 C5 ="Week "&WEEKNUM(B5,2)&" Year "&YEAR(B5) D5 =WEEKNUM(B5,2) E5 =YEAR(B5) F5 =CONCATENATE(D5,E5) G5 =COUNTIF('Job Data'!G:G,Summary!F5) H5 =LOOKUP(F5,'Call Data'!B:B,'Call Data'!C:C) I5 =LOOKUP(F5,'Call Data'!B:B,'Call Data'!D:D) J5 =LOOKUP(F5,'Call Data'!B:B,'Call Data'!E:E) B6 =B5+7 C6 ="Week "&WEEKNUM(B6,2)&" Year "&YEAR(B6) D6 =WEEKNUM(B6,2) E6 =YEAR(B6) F6 =CONCATENATE(D6,E6) G6 =COUNTIF('Job Data'!G:G,Summary!F6) B7 =B6+7 C7 ="Week "&WEEKNUM(B7,2)&" Year "&YEAR(B7) D7 =WEEKNUM(B7,2) E7 =YEAR(B7) F7 =CONCATENATE(D7,E7) G7 =COUNTIF('Job Data'!G:G,Summary!F7) B8 =B7+7 C8 ="Week "&WEEKNUM(B8,2)&" Year "&YEAR(B8) D8 =WEEKNUM(B8,2) E8 =YEAR(B8) F8 =CONCATENATE(D8,E8) G8 =COUNTIF('Job Data'!G:G,Summary!F8)
Excel tables to the web >> Excel Jeanie HTML 4
The purpose of this report is to work out number of phones calls done per job, I attempted to extract the call data with the fomula shown in cells H4,I4 and J4 but as you can see this only returns a #N/A. I believe this is being caused by the many blank cells between the data.
Anyone have any Ideas on how I can extract this data, New call data will be entered weekly always in the same format so hoping the formula I use will be a easily adaptable formula for future additions of data.
Regards,
Troy
A B C D E F G H I J K L M N O P Q R S T U V 2 Week 37 Year 2009 Report: Agent Group Performance by Period 3 Site ERA Communications Date range 07/09/2009  13/09/2009 05:45  16:00 4 Device: [iagroup01] 2216  2216 Created on: 20/01/2010 5 Created by: j.nicholls 6 Activity period ACD calls handled Non ACD calls handled Calls abandoned Calls outbound Calls requeued Calls transferred to agent Calls transferred from agent Conference calls Account codes ACD handling time (hh:mm:ss) Average ACD handling time (hh:mm:ss) Non ACD handling time (hh:mm:ss) Average non ACD handling time (hh:mm:ss) Outbound handling time (hh:mm:ss) Average outbound handling time (hh:mm:ss) Average manned agents 7 5:00 0 0 0 0 0 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0.0 8 6:00 0 0 0 1 0 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00 0:00:11 0:00:11 0.0 9 7:00 1 3 0 11 0 1 1 0 0 0:20:17 0:20:17 0:12:36 0:04:12 1:01:56 0:05:37 0.7 10 8:00 1 6 0 5 0 0 0 0 0 0:05:09 0:05:09 0:22:38 0:03:46 0:59:19 0:11:51 0.7 11 9:00 2 1 0 10 0 0 0 0 0 0:06:15 0:03:07 0:04:05 0:04:05 0:39:41 0:03:58 0.7 12 10:00 4 4 0 14 0 0 0 0 0 0:29:00 0:07:15 0:14:10 0:03:32 0:22:55 0:01:38 0.7 13 11:00 2 3 0 11 0 0 1 0 0 0:15:23 0:07:41 0:19:14 0:06:24 0:39:31 0:03:35 0.7 14 12:00 2 3 0 9 0 0 0 0 0 0:23:29 0:11:44 0:47:39 0:15:53 0:37:07 0:04:07 0.7 15 13:00 2 3 0 8 0 0 0 0 0 0:03:27 0:01:43 0:03:12 0:01:04 0:25:52 0:03:14 0.7 16 14:00 0 4 1 5 0 0 1 0 0 0:00:00 0:00:00 0:57:31 0:14:22 0:16:33 0:03:18 0.7 17 15:00 0 2 0 11 0 0 0 0 0 0:00:00 0:00:00 0:19:55 0:09:57 0:41:37 0:03:47 0.6 18 WeekNumYear Total Calls Inbound Outbound 19 372009 129 15 85 Total 14 29 1 85 0 1 3 0 0 1:43:00 0:07:21 3:21:00 0:06:56 5:44:42 0:04:03 0.6
Spreadsheet Formulas Cell Formula A2 ="Week "&WEEKNUM(LEFT(Q3,10),2)&" "&"Year "&YEAR(LEFT(Q3,10)) B19 =IF(Q3="","",WEEKNUM(LEFT(Q3,10))&YEAR(LEFT(Q3,10))) C19 =IF(SUM(G19:J19)=0,"",SUM(G19:J19)) D19 =IF(SUM(G19+I19)=0,"",SUM(G19+I19)) E19 =IF(J19="","",J19)
Excel tables to the web >> Excel Jeanie HTML 4
Call Data
A B C D E F G H I J K L M N O P Q R S T U V 20 21 Week 38 Year 2009 Report: Agent Group Performance by Period 22 Site ERA Communications Date range 14/09/2009  20/09/2009 06:00  16:00 23 Device: [iagroup01] 2216  2216 Created on: 21/01/2010 24 Created by: j.nicholls 25 Activity period ACD calls handled Non ACD calls handled Calls abandoned Calls outbound Calls requeued Calls transferred to agent Calls transferred from agent Conference calls Account codes ACD handling time (hh:mm:ss) Average ACD handling time (hh:mm:ss) Non ACD handling time (hh:mm:ss) Average non ACD handling time (hh:mm:ss) Outbound handling time (hh:mm:ss) Average outbound handling time (hh:mm:ss) Average manned agents 26 5:00 0 0 0 0 0 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0.0 27 6:00 0 0 0 0 0 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0.0 28 7:00 2 1 0 10 0 0 0 0 0 0:10:47 0:05:23 0:01:17 0:01:17 0:48:37 0:04:51 0.7 29 8:00 2 3 1 18 0 0 0 0 0 0:28:53 0:14:26 0:11:39 0:03:53 0:45:28 0:02:31 0.7 30 9:00 5 4 3 9 0 1 0 0 0 0:33:45 0:06:45 0:11:51 0:02:57 0:14:53 0:01:39 0.7 31 10:00 2 1 0 11 0 0 0 0 0 0:10:19 0:05:09 0:08:04 0:08:04 2:32:11 0:13:50 0.7 32 11:00 1 0 1 20 0 0 0 0 0 0:08:50 0:08:50 0:00:00 0:00:00 1:16:22 0:03:49 0.7 33 12:00 1 2 1 6 0 0 0 0 0 0:04:21 0:04:21 0:07:24 0:03:42 0:24:34 0:04:05 0.7 34 13:00 3 3 2 7 0 0 0 0 0 0:34:34 0:11:31 0:13:26 0:04:28 0:16:15 0:02:19 0.7 35 14:00 0 4 1 18 0 0 1 1 0 0:00:00 0:00:00 0:12:41 0:03:10 1:05:32 0:03:38 0.7 36 15:00 0 1 0 10 0 0 0 0 0 0:00:00 0:00:00 0:01:46 0:01:46 0:31:26 0:03:08 0.6 37 WeekNumYear Total Calls Inbound Outbound 38 382009 153 25 109 Total 16 19 9 109 0 1 1 1 0 2:11:29 0:08:13 1:08:08 0:03:35 7:55:18 0:04:22 0.6
Spreadsheet Formulas Cell Formula A21 =IF(A2="","",IF(AND(Q22="",A2="New Data Here"),"",IF(AND(Q22="",LEFT(A2,1)="W"),"New Data Here","Week "&WEEKNUM(LEFT(Q22,10),2)&" "&"Year "&YEAR(LEFT(Q22,10))))) B38 =IF(Q22="","",WEEKNUM(LEFT(Q22,10))&YEAR(LEFT(Q22,10))) C38 =IF(SUM(G38:J38)=0,"",SUM(G38:J38)) D38 =IF(SUM(G38+I38)=0,"",SUM(G38+I38)) E38 =IF(J38="","",J38)
Excel tables to the web >> Excel Jeanie HTML 4
The Two Above tables show how the data is formatted, Columns A through E have been added by me, F through V are an extract from our phone reporting software.
The below is the summary sheet were I am trying to collate the date
Summary
B C D E F G H I J 3 Week Year WeekNumYear Total Jobs Total Calls Total Inbound Total Out Bound 4 7/09/2009 Week 37 Year 2009 37 2009 372009 0 5 14/09/2009 Week 38 Year 2009 38 2009 382009 2 #N/A #N/A #N/A 6 21/09/2009 Week 39 Year 2009 39 2009 392009 5 7 28/09/2009 Week 40 Year 2009 40 2009 402009 3 8 5/10/2009 Week 41 Year 2009 41 2009 412009 11
Spreadsheet Formulas Cell Formula C4 ="Week "&WEEKNUM(B4,2)&" Year "&YEAR(B4) D4 =WEEKNUM(B4,2) E4 =YEAR(B4) F4 =WEEKNUM(B4,2)&YEAR(B4) G4 =COUNTIF('Job Data'!G:G,Summary!F4) B5 =B4+7 C5 ="Week "&WEEKNUM(B5,2)&" Year "&YEAR(B5) D5 =WEEKNUM(B5,2) E5 =YEAR(B5) F5 =CONCATENATE(D5,E5) G5 =COUNTIF('Job Data'!G:G,Summary!F5) H5 =LOOKUP(F5,'Call Data'!B:B,'Call Data'!C:C) I5 =LOOKUP(F5,'Call Data'!B:B,'Call Data'!D:D) J5 =LOOKUP(F5,'Call Data'!B:B,'Call Data'!E:E) B6 =B5+7 C6 ="Week "&WEEKNUM(B6,2)&" Year "&YEAR(B6) D6 =WEEKNUM(B6,2) E6 =YEAR(B6) F6 =CONCATENATE(D6,E6) G6 =COUNTIF('Job Data'!G:G,Summary!F6) B7 =B6+7 C7 ="Week "&WEEKNUM(B7,2)&" Year "&YEAR(B7) D7 =WEEKNUM(B7,2) E7 =YEAR(B7) F7 =CONCATENATE(D7,E7) G7 =COUNTIF('Job Data'!G:G,Summary!F7) B8 =B7+7 C8 ="Week "&WEEKNUM(B8,2)&" Year "&YEAR(B8) D8 =WEEKNUM(B8,2) E8 =YEAR(B8) F8 =CONCATENATE(D8,E8) G8 =COUNTIF('Job Data'!G:G,Summary!F8)
Excel tables to the web >> Excel Jeanie HTML 4
The purpose of this report is to work out number of phones calls done per job, I attempted to extract the call data with the fomula shown in cells H4,I4 and J4 but as you can see this only returns a #N/A. I believe this is being caused by the many blank cells between the data.
Anyone have any Ideas on how I can extract this data, New call data will be entered weekly always in the same format so hoping the formula I use will be a easily adaptable formula for future additions of data.
Regards,
Troy