Excel If statements and formulas 1

chanman525 · Sep 22, 2009

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 · Sep 22, 2009

Hi,

You need to have dates (REAL DATES): not just month strings to calcuate ranges.

faq68-5827

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

chanman525 · Sep 22, 2009

Skipvought,

Ok, that is not a problem to change. Do you suggest to have a date range like (2/1/2009 thru 2/28/2009) or just utilize 2/1/2009 to mark as the month of February?

The range of dates is the begin and end date and I am looking for something to compare the range of those two dates and determine if it falls into a particular month.

Thanks for the quick response to this post.

SkipVought · Sep 22, 2009

You can use the FIRST of each month. Then evaluate using >= date and < next date.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

chanman525 · Sep 22, 2009

Skip,
Could you oblige and post a sample string on how you would test for the conditions mentioned above based on the columns identified below?

The string would be placed into Cell E3 assuming that the first row (contains 4, 5, 4, 4, 5, 4 ) is row 1.

Code:

Columns
  A             B           C        D              E          F         g       H       I       J
____________________________________________________________________________________________________
                                                     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

chanman525 · Sep 22, 2009

skip,

I forgot to change the Named months to dates such as 2/1/2009. My apologies, can you use that assumption?

SkipVought · Sep 22, 2009

My results...
[tt]
4 5 [highlight blue][white]4 5 4 4 5 4[/white][/highlight]
begins ends 4Week 5Week [highlight red][white]Feb-09 Mar-09 Apr-09 May-09 Jun-09 Jul-09[/white][/highlight]
2/1/2009 2/28/2009 7591 0 $7,591
5/1/2009 6/30/2009 581 0 $465 $581
4/1/2009 7/31/2010 693 288 $231 $231 $288 $231
[/tt]
My formula
[tt]
C3: =SUMPRODUCT(([highlight red][white]rMonths[/white][/highlight]>=$A3)*([highlight red][white]rMonths[/white][/highlight]<=$B3)*([highlight blue][white]rWeeks[/white][/highlight blue]=C$1)*($E3:$K3))
[/tt]

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

chanman525 · Sep 22, 2009

Skip,
Again, thank you for the quick response. While I was putting the string in place that you gave me I realized a disconnect in what I was asking and what was perceived.

If I am not mistaken you added the values of the 4-5-4 weeks over the months and then placed the total into either the 4week or 5week column.

I think this was due to the way I posted the sample data. When in actuality what I need to do is place the value in the 4week or 5week column (this is a fixed number) under the month column if the begin and end dates fall into the particular month column.

So if I can put it into words....

If the begin date is 4/1/2009 and the end date is 7/31/2009 then I will need to put the value of the 4week column or the 5 week column for each month that the two dates fall in. So April, May, June and July would have values of $231, $231, $288, $231 respectively. If you notice June is a 5 week month so it has $288 where as the other months are 4week months so they have $231. If the Month does not fall into the begin and end dates then simply place a zero as a place holder.

Now not to complicate things or push my luck here.... If it is easier to add it now I would like to be able to determine if the start date between the 1st and the 15th and include it in the current month, however if it is greater than the 15th then it needs to move to next month.

Again, I appreciate the help and the time and hopefully I am not asking to much....

SkipVought · Sep 22, 2009

Here's the formula ...
[tt]
E3: =IF(AND($A3<F$2,$B3>=E$2),INDEX($C3:$D3,1,E$1-3),0)
[/tt]
Don't have a good answer for the 15th issue.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

SkipVought · Sep 22, 2009

Give this a try...
[tt]
E3: =IF(AND(IF(DAY($A3)>15,DATE(YEAR($A3),MONTH($A3)+1,1),$A3)<F$2,$B3>=E$2),INDEX($C3:$D3,1,E$1-3),0)
[/tt]

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

chanman525 · Sep 22, 2009

Skip,
Both statements appear to be working correctly, man I appreciate the time you gave me today.

I was envisioning a much more complex statement but you pulled it off making it look easy. Of course I would have never thought off using the INDEX() function and even if I did I probably would not have figured it out.

Kudos to you and your skills, Thank you.

SkipVought · Sep 22, 2009

Pleased to take the challenge.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

chanman525 · Sep 22, 2009

Well Skip, After getting this plugged in the actual spreadsheet I noticed that in the second statement that handles the greater than 15 of the month does not work. It simply leaves the month column value blank and does not move the value to the next month if the date is 2/16 or greater. I know you said that you said " don't have a good answer for the 15th issue" but just in case someone else sees this post they know that it is not handling it correctly when the date is 16 and greater, but it does work if it is 15 and lower.

Attempt to show data results;

Code:

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

SkipVought · Sep 22, 2009

[tt]=IF(AND(IF(DAY($A3)>15,DATE(YEAR($A3),MONTH($A3)+1,1),$A3)<F$2,IF(DAY($A3)>15,DATE(YEAR($A3),MONTH($B3)+2,0),$A3)>=E$2),INDEX($C3:$D3,1,E$1-3),0)[/tt]

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

chanman525 · Sep 23, 2009

Skip,
Once again your skills prove exceptional and what you provided works as expected. I have tried to dissect the statement and understand how it works but I can not figure out how to or moreover where to plug in an additional test for end date.

Could you oblige me with one more challenge to add this in to the current statement?

End Date: If the end date is less than or equal to the 15th then the end date is the current month MINUS one. If the end date is greater than the 15th then the end date is the current month.

Here is the pass that I made but I am not sure that it is correct. Not to mention I simply modified the statement you sent me to see if I could get it to work but not sure where it would plug into the original statement.

=IF(AND(IF(DAY($B3)>15,DATE(YEAR($A3),MONTH($B3)-1,1),$B3)<F$2,IF(DAY($B3)>15,DATE(YEAR($B3),MONTH($B3)+1,0),$A3)>=E$2),INDEX($C3:$D3,1,E$1-3),0)

chanman525 · Sep 23, 2009

I actually came up with this but it is not placing the values in the correct column the way the other one is. I know there is something wrong someplace but I believe that the testing of the date is working since I broke out the statement to work on it in pieces.

=IF(AND(IF(DAY($B3)>15,DATE(YEAR($B3),MONTH($B3)+1,0),$B3)<F$2,IF(DAY($B3)<15,DATE(YEAR($B3),MONTH($B3)-2,0),$A3)>=E$2,IF(DAY($B3)>15,DATE(YEAR($B3),MONTH($B3)+1,0),$B3)<F$2,IF(DAY($B3)<=15,DATE(YEAR($B3),MONTH($B3)-0,0))>E$2),INDEX($C3:$D3,1,E$1-3),0)

Making attempts here, just not getting on the right track.

SkipVought · Sep 23, 2009

[tt]
=IF(AND(IF(DAY($A3)>15,DATE(YEAR($A3),MONTH($A3)+1,1),$A3)<F$2,IF(DAY($A3)>15,DATE(YEAR($A3),MONTH($A3)+2,0),IF(DAY($B3)>15,$B3,DATE(YEAR($B3),MONTH($B3),0)))>=E$2),INDEX($C3:$D3,1,E$1-3),0)
[/tt]
The basic formula structure, using pseudo code is...
[tt]
If begins < CurrentMon+1 AND ends >= CurrentMon Then
Return_4_or_5_Week_Value
Else
Return ZERO
End

=IF(AND($A3<F$2,$B3>=E$2),INDEX($C3:$D3,1,E$1-3),0)
[/tt]
Then all I did when you added a new specification was to substitute another IF() function for that reference, that would return the value required...
[tt]
If begins < CurrentMon+1 AND ends >= CurrentMon Then
Return_4_or_5_Week_Value
Else
Return ZERO
End
[/tt]
[tt]
If beginsDAY > 15 Then
Return first of next month
Else
Return begin
End

IF(DAY($A3)>15,DATE(YEAR($A3),MONTH($A3)+1,1),$A3)

[/tt]
and so on for each new spec.

BTW, my last formula had a fundamental error, that, I believe, I fixed.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

chanman525 · Sep 23, 2009

Skip,
another issue I found.

It appears that in this last statement you sent me if the begin date is 15 or less then it only puts the value in the current month but if the end date is beyond the current month then it does not spread the value accross the months. But if it is 16 then it spreads the values.

For example;

Start is 2/15/2009 and end is 4/28/2009
I will get a 4wk for the month of February

Start is 2/16/2009 and end is 4/28/2009
I will get 4wk and 5wk values for the months of Feb, March and April.

statement reference;
=IF(AND(IF(DAY($A3)>15,DATE(YEAR($A3),MONTH($A3)+1,1),$A3)<F$2,IF(DAY($A3)>15,DATE(YEAR($A3),MONTH($B3)+2,0),$A3)>=E$2),INDEX($C3:$D3,1,E$1-3),0)

chanman525 · Sep 23, 2009

sorry on the previous post, it was posted before I realized you had responded.

SkipVought · Sep 23, 2009

No problem. Sorry for the error.

Do you get the expected results?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Excel If statements and formulas 1

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor