Date Formula Help Please!

[punukollu]

Hello, I am looking for a formula with the following conditions:

• The fiscal year of the business runs from December 30, 2007 to January 03, 2009
• There are 13 periods in the fiscal year ending with different date ranges. For example, period 01 runs between December 30, 2007 to January 26, 2008
• There may be 3 capital projects completed in Period 01, 5 in period 02, 6 in period 03 and so on

Task:
I need a formula where I can say - count the total number of projects from period 01 to Period 13 with the period end date restrictions for the fiscal year. The formula should be able to count the no of projects for each period individually.

Thank you very much for your help.

 

[iliace]

How can you determine between which dates each period runs?

How are the project completions listed?

 

[punukollu]

Accounting department sets the date ranges for each period.

The real estate department sets the date for the opening of the capital project.

 

[iliace]

How can you determine between which dates each period runs?

How are the project completions listed?

Is there a list somewhere? Or can you get one into your spreadsheet somewhere?

 

[punukollu]

Hey, Thank you for trying to help me...

Yes I do have a list. For example:

Jan 10, 2008 - Store No: 0007 open
Jan 15, 2008 - Store No: 1658 open....etc....

 

[iliace]

Hey, Thank you for trying to help me...

Yes I do have a list. For example:

Jan 10, 2008 - Store No: 0007 open
Jan 15, 2008 - Store No: 1658 open....etc....

This is the project list. I'm assuming you mean all in one column, or is Store No and open status in a different column?

You also need a list of periods, to match the dates to periods. Do you have that somewhere, or can you get it into the workbook?

Keeping in mind that no one here has ever seen your spreadsheet, be as specific as you can - range addresses, names of worksheets, which sample lists represent what, etc.

 

[punukollu]

Column A in Excel
Store No - Header
1245
2548
3698
2586
1256
4578

Column B in Excel
Opening Date - Header
31-Jan-08
12-Feb-08
03-Mar-08
04-Apr-08
13-May-08
18-Jun-08

Period Range dates
Period 01 - January 01, 2008 - January 31, 2008
Period 02 - February 01, 2008 - February 29, 2008
Period 03 - March 01, 2008 - March 31, 2008
Period 04 - April 01, 2008 - April 30, 2008
Period 05 - May 01, 2008 - May 31, 2008
Period 06 - June 01, 2008 - June 30, 2008

Fiscal year runs from January 31, 2008 - December 31, 2008 assuming there are 12 periods in the year.

Now, you can see that there is atleast 1 project opening in every period for the first six periods of the year. I need a formula which can count the number of projects in the period for the fiscal year automatically for all 12 periods.

Thank you very much for your help.....

 

[iliace]

Okay, so I have you sample data in columns A and B (A2:B7 for the six total stores) and your list of periods in column D (D2:D7 respectively). G1 is where the period number is (must be 2 digits). Here's the formula.... kinda ugly, but it works. You have to use Ctrl+Shift+Enter after you paste this into the formula bar; regular Enter won't work (array formula).

=SUMPRODUCT(--($B$2:$B$7>=--MID(INDEX($D$2:$D$7,MATCH(G1,MID($D$2:$D$7,FIND(" ",$D$2:$D$7)+1,2),0)), FIND("-",INDEX($D$2:$D$7,MATCH(G1,MID($D$2:$D$7, FIND(" ",$D$2:$D$7)+1,2),0)))+2,FIND("-",INDEX($D$2:$D$7, MATCH(G1,MID($D$2:$D$7,FIND(" ",$D$2:$D$7)+1,2),0)), FIND("-",INDEX($D$2:$D$7,MATCH(G1,MID($D$2:$D$7,FIND(" ",$D$2:$D$7)+1,2),0)))+1)-FIND("-",INDEX($D$2:$D$7,MATCH(G1, MID($D$2:$D$7,FIND(" ",$D$2:$D$7)+1,2),0)))-2)),--($B$2:$B$7<=--RIGHT( INDEX($D$2:$D$7,MATCH(G1,MID($D$2:$D$7,FIND(" ",$D$2:$D$7)+1,2),0)), LEN(INDEX($D$2:$D$7,MATCH(G1,MID($D$2:$D$7, FIND(" ",$D$2:$D$7)+1,2),0)))-FIND("-",INDEX($D$2:$D$7,MATCH(G1, MID($D$2:$D$7,FIND(" ",$D$2:$D$7)+1,2),0)),FIND("-", INDEX($D$2:$D$7,MATCH(G1, MID($D$2:$D$7,FIND(" ",$D$2:$D$7)+1,2),0)))+1)-1)))

Copy down as needed. If you can afford additional columns, here's how I came up with this solution.

This returns the matching text of the period (array-entered):

=INDEX($D$2:$D$7,MATCH(G1,MID($D$2:$D$7,FIND(" ",$D$2:$D$7)+1,2),0))

This takes that text (populated in H1) and extracts the period begin date, in J1:

=--MID(H1,FIND("-",H1)+2,FIND("-",H1,FIND("-",H1)+1)-FIND("-",H1)-2)

This extracts the period end date, in K1:

=--RIGHT(H1,LEN(H1)-FIND("-",H1,FIND("-",H1)+1)-1)

Here is the final sumproduct that returns the result:

=SUMPRODUCT(--($B$2:$B$7>=J1),--($B$2:$B$7<=K1))

Much simpler to edit, but uses more cells obviously. If you can live with hiding those columns, that would be your preferred solution.

 

[iliace]

<TABLE style="WIDTH: 470pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=626 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 266pt; mso-width-source: userset; mso-width-alt: 12946" width=354><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Store</TD><TD id=td_post_1599250 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=80>Date</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 266pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=354>Range</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Period #</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1245

</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>31-Jan-08

</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 01 - January 01, 2008 - January 31, 2008

</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>2548</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>12-Feb-08</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 02 - February 01, 2008 - February 29, 2008</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">02</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>3698</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3-Mar-08</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 03 - March 01, 2008 - March 31, 2008</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>2586</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>4-Apr-08</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 04 - April 01, 2008 - April 30, 2008</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">04</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1256</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>13-May-08</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 05 - May 01, 2008 - May 31, 2008</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>4578</TD>

<TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>18-Jun-08</TD>

<TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 06 - June 01, 2008 - June 30, 2008</TD>

<TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">06</TD></TR></TBODY></TABLE>

 

[punukollu]

Thank you very much. I will get to work tomorrow and will test it out and will keep you posted.

You are the best buddy!