# Date and if formulas

#### michyb

##### New Member
Hi there brains trust! Hope you are all well
I have a question involving dates and lookups/if formulas - clearly i am doing something wrong as i cant get the right answer
I am needing to calculate commissions monthly and include previous months paid and not paid in columns following but i cant seem to get the formulas to work.
Clearly I am doing something wrong and really need some advice have spent 2 weeks on this already!
I have colour coded for ease of following the spreadsheet (have removed names for obvious reasons) and formulas but if someone can see a glaringly obvious error please could you help.

Have an awesome day all

excel commission help1.xlsx
ABCDEFGHIJKLMNOPQRST
2
3October 2020Sales need to be closed within the month to be paid commission in that month, else they are paid in month finalised
4
5DateInvoice No.Customer PO No.Customer NameAmountAmount DueStatusMonth endDate Closed (date paid)Salespersonsalespersoninvoice nocommisspaidnot paid
6
73/10/202011485672146566/VO47289528340Closed4413611/12/20201148567n028340
85/10/2020114861121465392557.50Closed4413611/12/20201148611y#N/A#N/A0
927/10/20201148683295-5Credit441361148683n02950
1026/10/2020114869021379916882.70Closed4413611/12/20201148690y#N/A#N/A0
1126/10/20201148728216143678100Closed4413611/12/20201148728n078100
12
13IF(\$I12=\$B\$7,,IF(\$J12<\$B8,"n","y"))
14
15IF\$O12="n",,VLOOKUP(\$N12,\$C\$11:\$H\$152,4))
16
17IF(\$O12="y",VLOOKUP(\$N12,\$C\$11:\$H\$152,4),IF(\$O12="n",F12))
18
Sheet1
Cell Formulas
RangeFormula
N7:N11N7=C7
O7:O11O7=IF(\$I7=\$B\$7,,IF(\$J7<\$B3,"n","y"))
P7:P11P7=IF(\$O7="n",,VLOOKUP(\$N7,\$C\$11:\$H\$152,4))
Q7:Q11Q7=IF(\$O7="y",VLOOKUP(\$N7,\$C\$11:\$H\$152,4),IF(\$O7="n",F7))
S7:S11S7=IF(J7>I7,,F7)

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Marcílio_Lobão

##### Well-known Member
*michyb, Good morning.

Maybe I am missing something.

The lack of the argument in the IF function is not causing you problems?

 O7:O11 O7 =IF(\$I7=\$B\$7, ,IF(\$J7<\$B3,"n","y")) P7:P11 P7 =IF(\$O7="n", ,VLOOKUP(\$N7,\$C\$11:\$H\$152,4)) Q7:Q11 Q7 =IF(\$O7="y",VLOOKUP(\$N7,\$C\$11:\$H\$152,4),IF(\$O7="n",F7 , ,)) S7:S11 S7 =IF(J7>I7, ,F7)

I hope it heps.

#### A Durfani

##### Active Member
Is it what you want?

Book1
ABCDEFGHIJKLMNOPQR
1
2October 2020Sales need to be closed within the month to be paid commission in that month, else they are paid in month finalised
3
4DateInvoice No.Customer PO No.Customer NameAmountAmount DueStatusMonth endDate Closed (date paid)Salespersonsalespersoninvoice nocommisspaidnot paid
5
63/10/202011485672146566/VO47289528340Closed4413611/12/20201148567n 2834
75/10/2020114861121465392557.50Closed4413611/12/20201148611y2557.5
827/10/20201148683295-5Credit441361148683n 295
926/10/2020114869021379916882.70Closed4413611/12/20201148690y6882.7
1026/10/20201148728216143678100Closed4413611/12/20201148728y7810
11
12
13
14
15
16
17
Sheet1
Cell Formulas
RangeFormula
N6:N10N6=C6
O6:O10O6=IF(\$I6=\$B\$6,"",IF(\$J6<\$B2,"n","y"))
P6:P10P6=IF(\$O6="n","",VLOOKUP(\$N6,\$C\$6:\$H\$148,4))
Q6:Q10Q6=IF(\$O6="y","",VLOOKUP(\$N6,\$C\$6:\$H\$148,4))

#### michyb

##### New Member
ooh I will try that on my sheet and let you know

#### michyb

##### New Member
Is it what you want?

Book1
ABCDEFGHIJKLMNOPQR
1
2October 2020Sales need to be closed within the month to be paid commission in that month, else they are paid in month finalised
3
4DateInvoice No.Customer PO No.Customer NameAmountAmount DueStatusMonth endDate Closed (date paid)Salespersonsalespersoninvoice nocommisspaidnot paid
5
63/10/202011485672146566/VO47289528340Closed4413611/12/20201148567n 2834
75/10/2020114861121465392557.50Closed4413611/12/20201148611y2557.5
827/10/20201148683295-5Credit441361148683n 295
926/10/2020114869021379916882.70Closed4413611/12/20201148690y6882.7
1026/10/20201148728216143678100Closed4413611/12/20201148728y7810
11
12
13
14
15
16
17
Sheet1
Cell Formulas
RangeFormula
N6:N10N6=C6
O6:O10O6=IF(\$I6=\$B\$6,"",IF(\$J6<\$B2,"n","y"))
P6:P10P6=IF(\$O6="n","",VLOOKUP(\$N6,\$C\$6:\$H\$148,4))
Q6:Q10Q6=IF(\$O6="y","",VLOOKUP(\$N6,\$C\$6:\$H\$148,4))
this does work to a point :D
however, and this was my stuff up, if i have a date within the month its not picked up for some reason. can i modify the formula to > 1/10 and < 31/10? Im out of practice with formulas i dont use them enough

#### A Durfani

##### Active Member
this does work to a point :D
however, and this was my stuff up, if i have a date within the month its not picked up for some reason. can i modify the formula to > 1/10 and < 31/10? Im out of practice with formulas i dont use them enough
Dates in which column?

Replies
5
Views
123
Replies
1
Views
309
Replies
1
Views
64
Replies
6
Views
102
Replies
6
Views
106

1,130,130
Messages
5,640,300
Members
417,135
Latest member
zeusmining

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back