Excel If statements and formulas 1

[chanman525]

Could I get some help on getting some if statements kicked off with the data below? Here is what I am looking to do.

Example: if the service period covers May through July we will want the spreadsheet to show NULL values for Feb through April AND August through January. We will want to show the three months of May, June and July with dollar values in those cells on the 4-5-4 schedule. If the invoice was for $2,500 the values would be May = $769, June = $962 and July = $769

Now to add to the pain would it be possible to add this into the formula.

Start Date: IF the start date is between the first and fifteenth of the month the start period is the current month. IF the start date is greater than the 15th then the start date is the current month PLUS one. Basically the charge falls into the next month.
End Date: IF the end date is less than or equal to the 15th then the end date is the current month MINUS one. Basically the charge fails into the previous month. IF the end date is greater than the 15th then the end date is the current month.

The idea is that the begin and end date along with the 4 and 5 week values are plugged in and then the months populate accordingly across the page so there is no need to manually type the value for the 4 and 5 week data under each month.

I have truncated the data below just for ease of use and it contains only the fields that are a part of this post.
Thank you for your time, expertise and help.

Working in XP with Excel 2007.

4 5 4 4 5 4
begins ends 4Week 5Week February March April May June July
2/1/2009 2/28/2009 $7,591 $9,489 $7,591
5/1/2009 6/30/2009 $465 $581 $465 $581
4/1/2009 7/31/2010 $231 $288 $231 $231 $288 $231


I tried getting the data to align but it just is not getting there in the final post. the 454454 numbers at the top represent the 4 week or 5 week month so it should start on February. The values for each month should align with the begins and ends dates.

 

[SkipVought]

BTW,

Although it is always better to begin a design knowing ALL the requirements, this building process -- simple to more complex -- is textbook.

Start with the simplest control structure. Test and be sure it works. Then start adding complexity. Test and add more.

Pretty simple!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chanman525]

Skip,
Thanks for the pseudo code, that helps out especially for future usage.

In reference to having all the requirements, I did post that in the first entry so there was some method to my madness .

I still am not getting the anticipated data return based off of the requirements above, and it appears to be only in the end date string. All rules seem to work for the start date.

-Start Date: IF the start date is between the first and fifteenth of the month the start period is the current month. IF the start date is greater than the 15th then the start date is the current month PLUS one. Basically the charge falls into the next month.

-End Date: IF the end date is less than or equal to the 15th then the end date is the current month MINUS one. Basically the charge fails into the previous month. IF the end date is greater than the 15th then the end date is the current month.


If the start date is 3/16/2009 and the end date is 5/16/2009. With this range I should get data for April and May however it is only populating for April. --- break point = IF the end date is greater than the 15th then the end date is the current month

If the start date is 3/16/2009 and the end date is 5/15/2009 I get data for April, which is correct.

If the start date is 3/15/2009 and the end date is 5/14/2009 I get data for March and April, which is correct.

If the start date is 3/15/2009 and the end date is 5/16/2009 I get data for March, April, May, which is correct

 

[SkipVought]

[tt]
=IF(AND(IF(DAY($A3)>15,DATE(YEAR($A3),MONTH($A3)+1,1),$A3)<F$2,IF(DAY($B3)>15,$B3,DATE(YEAR($B3),MONTH($B3),0))>=E$2),INDEX($C3:$D3,1,E$1-3),0)
[/tt]
The only thing this does not do is
[tt]
begins ends
2/16/2009 2/25/2009
[/tt]
Will that ever happen?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chanman525]

Skip,
I doubt the scenario you mentioned will ever occur due to the information that is being tracked (service periods). Once again, Thank you for your time and taking on the challenge. I placed the statement into the sheet and all works well. Here is another star to show my appreciation for working with me over the past couple days on this.

 

[SkipVought]

As often happens as incrimental changes get incorporated, the overall object may get obscured. I actually reconstucted the formula, starting with the basic control structure and then added each 15-day criteria to the begins and ends dates.

Hope it all works for you.

BTW, you might find it interesting to play with the DATE() function.
[tt]
=DATE(Year(TODAY()), Month(TODAY()), 0)
[/tt]
or any date.

faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chanman525]

Skip,
Just when you thought this thread would die....;-)

I did notice that the statement does not like the transition from December 2009 to January 2010 but after it jumps that gap it does fine for Feb 2010 and so on until December 2010 and January 2011 meet up again?

Is that due to the test that it is doing for the verifying the before and after dates?

 

[SkipVought]

My test worked.

Check your MonthDates.

TIP: To enter First of the month dates, key in, for instance, feb 2009 and hit ENTER. Then COPY/DRAG (grab the little button in the LOWER RH corner of the selection) that cell thru the desired range.

The result: First of the month dates sequentially increasing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}