Need help figuring out date range formula for a pay schedule

[Tux0rz]

Hello.
I am new to the forum and am trying to teach myself Excel. I know some of the most basic functions, but I am trying to build a spreadsheet that is quite advance for me.
I am a truck driver and I own my own truck. The company I'm leased to pays on a schedule outlined by our payroll department like this:
CUT OFF FOR PAY WEEK.pdf

What I would like to do is figure out a spreadsheet that will track my loads/trips by load and delivery dates and allow me to figure out whether I will receive a settlement during a given week. This week, for instance, I did not receive a settlement because, apparently, the loads I delivered four weeks ago didn't fall within the cutoff. The reason for this is that, and here's the wildcard, payroll paid me early on one load. Every so often, to help me out, he will throw one in early to get me a bigger settlement for the week. It's not a bad thing until, like this week, I don't get a settlement when I was expecting one. I don't fault payroll, I just want to be able to forecast ahead of time whether I will be receiving a settlement so I can plan my weekly budget better.

I realize this is confusing (at least to me) and may not be able to even be accomplished, but knowing how powerful Excel is, I figured I'd give it a try. When I was an IT Professional, I had a friend who was an Excel wizard, but since I left IT and went back to trucking, I don't have that resource. Don't know what you got til it's gone...

If you need more information, I'll give you what I can.

Thanks for any and all help and I look forward to being a member of what appears to be a great group and excellent source of information.

[Estevaoba]

Hello!

I use helper columns to list dates that are due for settlements. The array formula below will check from dynamical ranges of 7 rows (one week), if there has been trips, as per column G.

=IF(AND(WEEKDAY(A7)=6,SUM(OFFSET(G7,,,IFERROR(SMALL($K$7:$K$105,COUNTIF(K$7:K7,">0")),ROW())-IFERROR(SMALL($K$7:$K$105,COUNTIF(K$7:K7,">0")+1),0),1))>0),OFFSET(A7,28,0),"")

If you have to edit this formula, make sure you hit Ctrl+shift+enter keys simultaneously when you're done editing.

Column G will number the trips entered in columns C thru F, as I have entered as per info in your pdf file.
As you enter more trips, they will be numbered up in column G, and respective settlement due date will show in subsequent Friday row in helper column J and copied without blank rows in column H.

Blank rows 1 thru 5 are there to aid formula with upward OFFSET function. You can use them for header or something, but please don't delete them.

Please run some tests and let us know if it will work.

Good luck!

[Tux0rz]

Wow!

Thank you so much for taking the time out to work on this for me. I'm sorry it has taken so long to get back to you on this, but with Christmas coming up, I've been running pretty hard.
I think we're on the right track, but I think the fact that my payroll guy sometimes pays me early on some loads muddies the waters. Also, I end up sometimes having an outbound load land on the same day as an inbound load. Crazy, I know, but that happens, sometimes. I did a few entries into the spreadsheet and this is what I came up with:
2017-12-21.png

Things don't seem to be lining up very well and I have no idea how to correct it.

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Estevaoba]

Hi.

I have made some changes to make it simpler and included your new sample data.

Let's see how it goes.