- Joined
- Feb 1, 2012
- Messages
- 2
- Reaction score
- 0
Hi All,
I'm putting together a drawing register so I have two columns of dates. Column A is the forecast issue date and B is the actual issue date. It sounds simple but I need to count the number of dates in column A that fall within the current week AND that also have an issue date of anything before the start of this week. (a drg that is issued early to forecast)
It's the 'within this week' part i'm struggling with because to work that out you need two bits of logic (greater than the start of the week AND less than the end of the week) and I can't seem to nest that within one of the COUNTIFS criteria.
This is what i tried (D2 is 01/02/12):
=COUNTIFS(A1:A5,(AND(">="&$D$2-WEEKDAY($D$2,3),"<="&$D$2-WEEKDAY($D$2,3)+6)),B1:B5,"<"&$D$2-WEEKDAY($D$2,3))
A B
01/01/2011 01/01/2011
02/01/2011 02/01/2011
05/05/2011 05/05/2011
02/02/2012 01/12/2011
03/02/2012 30/01/2012
Any help greatly appreciated!
I'm putting together a drawing register so I have two columns of dates. Column A is the forecast issue date and B is the actual issue date. It sounds simple but I need to count the number of dates in column A that fall within the current week AND that also have an issue date of anything before the start of this week. (a drg that is issued early to forecast)
It's the 'within this week' part i'm struggling with because to work that out you need two bits of logic (greater than the start of the week AND less than the end of the week) and I can't seem to nest that within one of the COUNTIFS criteria.
This is what i tried (D2 is 01/02/12):
=COUNTIFS(A1:A5,(AND(">="&$D$2-WEEKDAY($D$2,3),"<="&$D$2-WEEKDAY($D$2,3)+6)),B1:B5,"<"&$D$2-WEEKDAY($D$2,3))
A B
01/01/2011 01/01/2011
02/01/2011 02/01/2011
05/05/2011 05/05/2011
02/02/2012 01/12/2011
03/02/2012 30/01/2012
Any help greatly appreciated!
