Date and if formulas

michyb

New Member
Joined
Mar 1, 2021
Messages
3
Office Version
  1. 365
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
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
*michyb, Good morning.

Maybe I am missing something.

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

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)

I hope it heps.
 

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
256
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Mar 1, 2021
Messages
3
Office Version
  1. 365
ooh I will try that on my sheet and let you know :)
 

michyb

New Member
Joined
Mar 1, 2021
Messages
3
Office Version
  1. 365
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 :)
Thankyou for your help in advance!
 

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
256
Office Version
  1. 2013
Platform
  1. Windows
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 :)
Thankyou for your help in advance!
Dates in which column?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top