Counting Weeks INVOLVED Between 2 Dates
Nov 3, 2008
I've been having problems coming up with a formula that will take a start date and an end date and come up with the number of weeks INVOLVED within this date range (each week being a Sunday through Saturday).
My problems is that the start and end dates could be any day of the week and not necessarily the same day (meaning divisible by 7 doesn't always work). I tried using ROUND((A2-A1)/7,0)+1 where A1 was the start date...and A2 was the end date.
The problem I had was if I picked a Monday as the start date, and went 12 days out (The saturday of week2)...it came up stating 3 weeks were involved - AND if I selected a Friday start date and picked the following monday in week 2 - the result was 1 week involved when 2 different weeks were involved.
View 4 Replies
ADVERTISEMENT
Mar 21, 2007
I need to make formulas to count the number of weeks that each phase of a project takes, but I currently have the start and end dates of the phases. (See attachment)
View 5 Replies
View Related
Jun 27, 2013
Is there a way to convert data that has dates to week by week? such was week 1 week 2 week 3 etc....
View 8 Replies
View Related
Jul 20, 2006
I require a worksheet function or group of function that will allow me to
convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4.
View 10 Replies
View Related
Jan 14, 2009
"Decision Date". It is a numerical date (ex: 1/5/09 indicating January 5th 2009).
I need to turn that date into the week that it falls into within a particular quarter of a year.
1/5/09 would be Week 2 (it is in the second week of January, and 2nd week of the quarter)
2/3/09 would be week 6 (6th week of the quarter).
And then I need the formula to start over once each quarter restarts... for example, April 1st would be week 1 (1st week of the second quarter).
View 9 Replies
View Related
Jan 13, 2014
find the formula to calculate how many weeks between two dates for example. I how many weeks between
December 23, 2013
January 5, 2014 = 2w
January 6, 2014
January 19, 2014 =2w
January 20, 2014
February 2, 2014
View 2 Replies
View Related
Dec 17, 2006
a sample of my sheet first....
View 9 Replies
View Related
Oct 16, 2009
I have a large spreadsheet which lists individual dates over two years and circulation figures next to each date. Sometimes there are two entries for a particular date e.g. 1/10/08 there were 150 readers of magazine A and 200 readers of magazine B. When displaying this on a graph it give a bar for each day, whereas I would like a bar for each week.
Is there a formula to convert the individual dates into weeks and then total the circulation figures for that week?
Column A = Dates
Column B = Circulation Figures
View 9 Replies
View Related
Jul 16, 2014
Another thread that wanted to count "weekends" got me to thinking about this one:
Count the full weeks (from Monday thru Sunday) between 2 dates.
For example...
Data Range
A
B
C
D
E
F
1
Date
Weekday
------
Start
End
Weeks
2
8/1/2013
Thu
8/1/2013
8/15/2013
1
3
8/2/2013
Fri
[Code] .........
Full Monday thru Sunday weeks = 1
This array formula** entered in F2:
=(MAX(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=7,ROW(INDIRECT(D2&":"&E2))))-MIN(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=1,ROW(INDIRECT(D2&":"&E2))))+1)/7
** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key then hit ENTER.
The logic of the formula is:
The max Sunday date within the date range minus the min Monday date within the date range +1 = total days divided by 7 (days in a full week) = full weeks
View 14 Replies
View Related
Feb 4, 2010
In Cells B2:B100, i have dates that which have been entered using a combo box (the dates type is for e.g. 14th March 2010 format)
I want a formula that will count the cells that have dates between 1st April 2010 to 30th June 2010 in cells B2:B100
Also, I would like a formula that counts weekend dates between 1st April 2010 to 30th June 2010?
View 9 Replies
View Related
Sep 7, 2009
i currently have a what if statement
=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")
I need it to also give an option of between 3 wks and 6 weeks
Therefore all options are
3-6 WKs
< 3 WKs
> 6 WKs
I have mastered 2 but now need a 3rd.
View 7 Replies
View Related
Feb 25, 2014
I'm having a complex issue that I've tried solving with =SUMPRODUCT(SUMIFS( and regular old =SUMIFS(, but all to no avail. Here is the issue at hand:
I need to look up three criteria, one of which is a text column.
Column A - Qualifier, is this a qualified sale (yes or no)?
Column B - Salesperson, who had the sale (Jacob, Charles, etc)
Column C - Date, when did this happen (2.2.14, 2.14.14, etc)
I need these three criteria to return a sum of Column A - how many are a yes. The formula that typically works for me for such a big sum, when only numbers are involved for the summing, is:
=SUMIFS(A:A,B:B,"Salesperson name",C:C,"Coordinating date")
I've uploaded how the final product should look across.
View 1 Replies
View Related
Jun 5, 2009
I need to sort a dataset by a column that may sometimes contain blanks. I'm sorting by time in decending order however excel is keeping any blank rows at the top.
Is there a way i can sort the column decending but keep the blanks at the bottom?
View 3 Replies
View Related
Dec 6, 2011
I have an array which I am attempting to use a simple Product function on. If one of the cells does not have a number, but instead contains 'N/A' or some other type of text, how do I get my Product function to result in 'N/A' rather than having the Product just ignore the text.
I cannot attach an XLS file but just pretend Cells A1:H1 read 1, 2, 3, N/A, 5, 6, 7, 8.
View 7 Replies
View Related
Sep 12, 2012
How do I post a formula without hidden rows being involved?
View 1 Replies
View Related
Jan 11, 2005
I have a column that I would like to have a count of all the dates that areless then today's date.
I have tried: =countif(d2:d25,"<TODAY",0) This has only gotten me 0. If I leave the " " out then I get an error message.
View 4 Replies
View Related
Jun 1, 2009
I have a range of ten cells (B4:K4), some of these cells contains dates, and other cells contains text, what I want is counting number of cells that contain dates earlier than today's date. I actually tried the following code, but it returns zero value.
View 2 Replies
View Related
Oct 9, 2007
a count formula.
Right, Column A on my sheet is the date that the work was completed. First thing is I need to be able to count how much work was completed per month. (I dont know why we have it all in one big sheet rather than monthly sheets but thats too logical!)
Secondly, column I is the number of days that piece of work took to complete. I need to be able to have a formula that looks at the month the work was completed, then total up the number of days that the work took.
(Example, if I have three bit of work completed in October, 1 took a total of 20 days, 1 took 15 and 1 took 10 it would = 45 days)
I know that this should be possible as I've had Excel doing more complex formulas than this.
View 14 Replies
View Related
Apr 29, 2009
Column A contains names
Column B their unique ID number
Column C the date a transaction occurred
How can I count the number of entries between two dates i.e. 1-31 April regardless of duplication or anything? I've tried a few things mentioned in other threads but none seem to work.
View 4 Replies
View Related
Oct 7, 2011
I need a formula to count the months (including part months) between 2 dates.
So i want 01/04/11 to 5/5/11 to equal 2
I have tried a few formulas that get the answer 1 but i need it to count the part month too.
View 2 Replies
View Related
Mar 4, 2013
I'm trying to count the number of cells that contain a date in a filtered row. I've been using the following formula, but it keeps coming back with a value of 0, even though there are 3 in the filtered row;
=SUMPRODUCT(--('Fixture Status'!J3:J1000=">=0"),SUBTOTAL(3,OFFSET('Fixture Status'!J3,
ROW('Fixture Status'!J3:J1000)-ROW('Fixture Status'!J3),0)))
View 1 Replies
View Related
Jan 27, 2014
Is there a formula I can use to add the amount of cells that contain just dates ?
View 6 Replies
View Related
Jun 4, 2007
I have a worksheet that has several dates in column D. I would like a formula to count the number of dates that are 7 days or less than the current date in F2.
View 9 Replies
View Related
Aug 30, 2007
I have a list of dates in column A and a list of dates in column B. I need to count the number of dates in column B that are before or equal to the date in column A. For example....
A B
05/14/06 05/28/07
08/07/07 08/06/07
08/09/07 08/09/07
08/25/07 08/27/07
... would yield an answer of 2.
View 9 Replies
View Related
Nov 28, 2009
I'm new to the forums, though I've solved a couple of my problems by lurking here.
I'm currently having a problem with a worksheet I've been working on at work. I'm not very experienced in Exel and have inherited a worksheet that keeps track of the machines we do maintenance on during the year. What I have now is a spreadsheet with columns alternating between initials and date, and rows with the machine names all the way down. It looks similar to this :
Name of machine Initials date initials date initials date initials date etc.
machine 1
machine 2
machine 3
machine 4
...
machine 121
I'd like to keep this format, but we need to keep track of who does what within a certain week, which means I need to count the number of instances of an initial, then compare it with the date in the column next to it, which I don't think would be a problem with just two columns, but I'm having problems wrapping my brain around how to do so with 20-30 columns.
I'm using date ranges in some of my calculation formulas, which I think necessitates the date being in a separate column than the text.
View 9 Replies
View Related
Nov 4, 2009
i have attached a copy of the file im working in. Its a spreadsheet to highlight when peoples reviews need doing. I have a summary at the top counting the number of overdue, upcoming and ok reviews. Thing is the formula for counting my upcoming reviews is incorrect and i can't seem to fix it.
View 3 Replies
View Related
Jul 6, 2012
I have collumn with different dates. I would like to count number of date with specific month in them (for example "june"). I tried it with if function, but something is missing.
Example of data:
1.6.2012
3.6.2012
9.6.2012
30.6.2012
[Code]...
My function: =SUMPRODUCT(IF(A1:A9;IF(MONTH(A1:A9)=6;1;0))) what shoud I correct or of better function?
View 9 Replies
View Related
Dec 2, 2013
I have a list of dates in column A as shown below & I want to get a count formula in column B for the # of dates in sequence. When the date sequence breaks a new count should be done at the end of the next sequence. The formula should be 1 I can put in cell B1 and copy down so that there are blanks & a count is only done at the end of the date sequence like in cell B4 or B7 below.
Col A Col B
Date Count
1 12-Oct
2 13-Oct
3 14-Oct
4 15-Oct4
5 17-Oct
6 18-Oct
7 19-Oct3
[Code]....
View 4 Replies
View Related
Jan 23, 2009
I have items arriving on certain dates (the dates are listed in column N)
In the cell to the right of each month, I want to have a formula that will count the dated cells in column N that fall under each month.
i.e. In cell B5, next to January, I want to display the total number of cells that contain a date in January (ultimately giving me the number of items that arrive in January) the number would read '5' because there are 5 dates in January that are in the list.
View 9 Replies
View Related
Feb 14, 2012
Any easy formula to compare two columens of fixed dates and count how many of each fall within a certain range of each other.
So, for example:
Col 1 Col 2
20/1/11 21/1/11
20/1/11 31/1/11
18/1/11 24/1/11
Lets say col one is a start date and col 2 is an end date. Now say I want to count the number of times the end date falls within8 days of the start
I would have thought that something as simple as
Code:
sumproduct(--(a1:a3<(b1:b3+8))
would work, but clearly I am making a fundamental error, and no matter what I try I'm getting stuck.
View 3 Replies
View Related
