Finding a date that is greater than or equal to an input date based on many criteria.

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
59
I've been trying to solve this problem for years, and may have even asked here a couple of years ago. But for most of this year, the world has been on pause so I've not needed to worry about it. But, basketball will hopefully be starting up again next year and it's time to solve it because how they pay officials has gone digital.

The pay rates are increased first of march each year. They can also be changed at any time, and if an official is upgraded the rates also increase. The rates depend on whether the gme being officiated is a regular season game or a finals game. There are also several competition types: Junior Domestic, Senior Domestic, After School Domestic, Miniball Domestic and then finals rates for each of those competitions.

Rates for U9 boys are different for U9 girls because the U9 girls is classed as a different competition type.

As if this wasn't bad enough, the rate is different for the last 3 games on a Saturday, and all games on a Sunday. (+$4 and +$1 respectively).

The only solution I've come up with so far is a massive table for every day of the year for the last 10 years and for the next 10 years (Hopefully I will still be officiating in 10 years). That's huge!

My preference is to use an effective date and match on age, day, gender, competition type and whether regular season or finals.

I've looked around for commercial and open source software but haven't come across anything. I also have 10 years of game data already recorded in Excel that could potentially be a huge task getting into some other program.

I'm assuming this is going to be a [H|V]Lookup but I'm damned if I can figure out the correct formula.

If the game date is > last pay rate row, use the last row.
If it's less than the date of the last row, I need to find the row between two dates that this game date sits between.
If the game date is < the first pay rate row - throw an error

Can any one help me figure this out?

thanks
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,631
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please Upload an example file with XL2BB addin (this is very better) or Image and show exactly what you want.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
I've been trying to solve this problem for years, and may have even asked here a couple of years ago.
Is it this?

If so, unless the layout of the data or the requirement has become considerably simpler, I expect the result will be similar. :(
 

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
59
Oh, ok, I didn't find that post on a search.

The data is never going to get simpler. Things become more complex over time, never simpler. The new digital pay system is a complete mess, and the people administering it have no idea what they are doing. I guess trying to code around the incompetence of people is a waste of time. In 24 hours, 46 people had complained they'd been paid wrong, but none of them could provide detailed evidence of what they should have been paid, and the organization couldn't figure it out either. Lucky I'm not going back until feb.

I guess it's time to start writing SQL/CF.

I shall stop asking for help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Oh, ok, I didn't find that post on a search.
Two easy ways for you to find that. At the top of the Excel Questions forum ..

1. Click one of these:
1608010607871.png


2. On the right hand side, use the Filter:
1608010721330.png
 

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
59

ADVERTISEMENT

Well, I sort of have a temporary solution whilst I write a web app for this.

I set up a sheet with all of the rates for boys, girls, women, men, after school for regular season and for finals. It also caters for the times a referee has a trainee assigned to them as they get paid for that too. Then the payments sheet is populated with this formula.

Every rate for every day of the year for 8 years so far. Not ideal, but it works.

Excel Formula:
=IF($D6="","",
IF(AND(Summary!$AJ5="J",Summary!$I5="R",Summary!N5>0,Summary!$V5="F"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$B5:$Q$3193,Summary!N5-6,0),
IF(AND(Summary!$AJ5="J",Summary!$I5="R",Summary!N5>0,Summary!$V5="M"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$B5:$AF$3193,Summary!N5+10,0),
IF(AND(Summary!$AJ5="S",Summary!$I5="R",Summary!N5>0,Summary!$V5="F"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$B5:$Q$3193,16),
IF(AND(Summary!$AJ5="S",Summary!$I5="R",Summary!N5>0,Summary!$V5="M"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$B5:$AG$3193,32),
IF(AND(Summary!$AJ5="A",Summary!$I5="R",Summary!N5>0,Summary!$V5="F"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$B5:$R$3193,17),
IF(AND(Summary!$AJ5="A",Summary!$I5="R",Summary!N5>0,Summary!$V5="M"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$B5:$AH$3193,33),
IF(AND(Summary!$AJ5="A",Summary!$I5="F",Summary!N5>0,Summary!$V5="F"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$AM5:$BC$3193,17),
IF(AND(Summary!$AJ5="A",Summary!$I5="F",Summary!N5>0,Summary!$V5="M"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$AM5:$BS$3193,33),
IF(AND(Summary!$AJ5="J",Summary!$I5="F",Summary!N5>0,Summary!$V5="F"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$AM5:$BB$3193,Summary!N5-6),
IF(AND(Summary!$AJ5="S",Summary!$I5="F",Summary!N5>0,Summary!$V5="M"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$AM5:$BR$3193,32),
IF(AND(Summary!$AJ5="S",Summary!$I5="F",Summary!N5>0,Summary!$V5="F"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$AM5:$BB$3193,16),
IF(AND(Summary!$AJ5="J",Summary!$I5="F",Summary!N5>0,Summary!$V5="M"),VLOOKUP(Summary!$E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$AM5:$BQ$3193,Summary!N5+10),0)))))))))))))

That calculates the game payments for the first time slot. There are up to 8 time slots per session. This calculates the trainee payments.

Excel Formula:
=IF('R:\Basketball\[Ref Stats v8.xlsx]Games'!$BJ5=0,0,
IF(Summary!H5="J",VLOOKUP(Summary!E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$B$5:$AI$3193,34,0)*'R:\Basketball\[Ref Stats v8.xlsx]Games'!$BJ5,
IF(Summary!H5="S",VLOOKUP(Summary!E5,'R:\Basketball\[RefRates.xlsx]Base Rates'!$B$5:$AJ$3193,35,0)*'R:\Basketball\[Ref Stats v8.xlsx]Games'!$BJ5,0)))

Next I cater for the games where there is only one referee (Which are called solo games). They get 1.5 game fee for solos...

Excel Formula:
=IF(AND('R:\Basketball\[Ref Stats v8.xlsx]Games'!$I5<>0,'R:\Basketball\[Ref Stats v8.xlsx]Games'!$J5=0),F6*0.5,0)
+IF(AND('R:\Basketball\[Ref Stats v8.xlsx]Games'!$N5<>0,'R:\Basketball\[Ref Stats v8.xlsx]Games'!$O5=0),F6*0.5,0)
+IF(AND('R:\Basketball\[Ref Stats v8.xlsx]Games'!$S5<>0,'R:\Basketball\[Ref Stats v8.xlsx]Games'!$T5=0),F6*0.5,0)
+IF(AND('R:\Basketball\[Ref Stats v8.xlsx]Games'!$X5<>0,'R:\Basketball\[Ref Stats v8.xlsx]Games'!$Y5=0),F6*0.5,0)
+IF(AND('R:\Basketball\[Ref Stats v8.xlsx]Games'!$AC5<>0,'R:\Basketball\[Ref Stats v8.xlsx]Games'!$AD5=0),F6*0.5,0)
+IF(AND('R:\Basketball\[Ref Stats v8.xlsx]Games'!$AH5<>0,'R:\Basketball\[Ref Stats v8.xlsx]Games'!$AI5=0),F6*0.5,0)
+IF(AND('R:\Basketball\[Ref Stats v8.xlsx]Games'!$AM5<>0,'R:\Basketball\[Ref Stats v8.xlsx]Games'!$AN5=0),F6*0.5,0)
+IF(AND('R:\Basketball\[Ref Stats v8.xlsx]Games'!$AR5<>0,'R:\Basketball\[Ref Stats v8.xlsx]Games'!$AS5=0),F6*0.5,0)

And finally calculate how much should have been paid with...

Excel Formula:
=SUM(F5:M5)+S5+Q5+R5
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Wow, you have put a lot into that! 🤪
 

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
59
Wow, you have put a lot into that! 🤪
😳 Actually, took half a day to do everything including setting up the rates table, and modify other sheets that depended on the old formats.
But yes, I've been building this excel system for about 8 years.
And I must thank all the people here who have provided solutions for this project over that time.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,531
Messages
5,625,369
Members
416,098
Latest member
jpmiralrio

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