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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Please Upload an example file with XL2BB addin (this is very better) or Image and show exactly what you want.
 
Upvote 0
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. :(
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top