I am trying to figure out out to set up the formula. I

have sales team who are paid by commission.First 30 days, they get commission based on the

activation after sold date. Also, they get 2nd

commission from 31st to 60 days if the device shows

activation. If after 60 days, they are not eligible for

commission at any point.

We're glad to hear that you are trying to set up a formula. If you need any help, feel free to ask. ;-)

Hi, From your post, it looks like you want a formula that returns a higher rate of commission if activation occurs within 30 days of the sold date, a lower rate if activation is between 31 and 60 days and no commission if the interval is longer.

Ifthat is what you are looking for, then try this

Place Sold date in cell A6

Place Activated date in cell B6

and in C6 place this formula:=IF(B6-A6<31,10%,IF(B6-A6<61,5%,0%))

As you didn't say what the commissions are, I just used 10%, 5% 0%To protect against an erroneous result when one or other date is empty add a test for empty cells, and the formula becomes:

=IF(OR(A6="", B6=""),"",IF(B6-A6<31,10%,IF(B6-A6<61,5%,0%)))Regards

there won't be any % on commission. It is going to be a flat

rate.$25 for first 30 days and $35 for between 31-60 days.

Hi, You can just replace the percentages in the formula with your values.

10% becomes 25, 5% becomes 35 and 0% becomes 0, and Format the cell with a currency format.

On re-reading your post, I am not clear - are there three dates or just two -

Can staff get both $25 & $35 commission, or is it one or the other.Regards

Ask Your Question

Weekly Poll