'If' formula

[Hendee70]

It must be early monday morning because I can not wrap my head around this.

I have a spreadsheet that has due dates and pay dates that are different
depending on sales rep. This is an example:

A B C D E
1 customer sales rep inv date due date pay date
2 misc 1 SLS 11/30/2009
3 misc 2 HOU 11/30/2009
4 misc 3 NAG 11/30/2009
5 misc 4 KAS 11/30/2009



Sales Rep SLS always gets paid on the first of the month following the
invoice date.
Sales Rep NAG always gets paid on the 15th if in by the 30th and paid by the
30th if in by the 15th.
Sales Rep KAS and HOU is net due 30 days.

Can anyone help?

 

[Paul C]

I would assume that your data contains many different reps with terms.

I would probably start with use the choose funtion.

CHOOSE(index_num,value1,value2,...)

Make a table that equates each rep with with set of terms (make these a
number so they can go dieectly into the Choose funtion).

You can than do something like Choose(Vlookup(Rep,TermsTable,2,false) to get
a number 1-whatever representing each set of terms,

You can then put formulas to get you date for each type.

If this helps, please remember to click yes.

 

[Hendee70]

There are only the four reps. Wouldn't it be easier to use the if function? I
am pretty well versed in Excel, with exception to these formulas. I can use
the if but I am not sure how to do it with more than on set.

 

[Pete_UK]

You could approach it like this in D2:

=IF(B2="SLS",DATE(YEAR(C2),MONTH(C2)+1,1),0) + IF(B2="NAG",IF(DAY(C2)
<=15,DATE(YEAR(C2),MONTH(C2),30),DATE(YEAR(C2),MONTH(C2)+1,15)),0) + IF
(OR(B2="KAS",B2="HOU"),C2+30,0)

Note that 30th February does not exist, so NAG might have a due date
of 1st (if leap year) or 2nd March.

Then copy down as required.

Hope this helps.

Pete

 

[Hendee70]

There is something wrong with the formula. When I use it I get an error
message about too many arguements. I do not have my glasses with me today,
and I am having a problem trying to find the error.

 

[Pete_UK]

Maybe you have missed a bracket or a comma in typing it into your
sheet. Here is a direct copy from my formula bar with D2 selected:

=IF(B2="SLS",DATE(YEAR(C2),MONTH(C2)+1,1),0) + IF(B2="NAG",IF(DAY(C2)
<=15,DATE(YEAR(C2),MONTH(C2),30),DATE(YEAR(C2),MONTH(C2)+1,15)),0) + IF
(OR(B2="KAS",B2="HOU"),C2+30,0)

Note that it is all one formula. Perhaps you can copy it from here,
click in D2 and paste it directly into your sheet.

Hope this helps.

Pete