I have an app where truck drivers log loads. Each load is a new row in a Load Log table. I want to calculate their estimated pay for the week Monday-Sunday.
I’m not really sure how to add up the logs for that user each week and then reset it. I was thinking of somehow logging the total pay each week so they could have a history. I just don’t know how to do it from a range Monday-Sunday.
Daniel - this will be possible using Glide Date Math. I’ll have a go at it today (and I may or may not come up with a method), but I’m sure once Jeff sees it he will give you nice succinct solution. In the meantime, have a read of the below thread - might give you some inspiration.
I read it that he wants to do it for the full week.
My initial thought would be to assign a week (of the year) number to each date, and use that to create self-relations and rollups to calculate the total for each week. Just getting the week number using Glide math is the tricky bit (for me).
@Daniel_O - I’ve put together a demo to show how this can be done.
It’s copyable, so feel free to use any or all of it, or ignore it completely
You can select any date, and it will return all the invoices from the selected driver for the week beginning the previous Monday.
Everything is contained in the Invoices tab and table. Let me know if you have questions.
(You can ignore all the other sheets/tabs, this app is one I use as a sandbox when I’m answering forum questions)
Edit: I’ve loaded about a months worth of dummy invoices in it, ranging from mid January to today. So if you select today’s date, or any date in the future, you won’t get any results.
The Glide Weeknum() function is different from the Google Sheets function of the same name, in that it only accepts one parameter. So that won’t work. To start the week on a Tuesday, adjust the formula to look like this:
To start the week on Wednesday, use Date-2
@Jeff_Hager - the above seems to work, does it look right to you?
@Daniel_O The WeekNum function in google sheets is different from the Weeknum function in Glide, so I don’t think the glide version will accept additional parameters like that. (Edit: Just realized you already wrote that in your reply @Darren_Murphy. I need to improve my reading skills. )
@Darren_Murphy That’s pretty much what I was thinking. Just offset the date by the required number of days. But since weeks are Sunday based, wouldn’t it need to be 2 days to get to Tuesday instead of 1 day? Also, wouldn’t we need to add days instead of subtract? Haven’t had my coffee yet, so I’m trying to mentally process this. I’m also trying to think of what would happen at the end or beginning of the year when the year changes and the week reverts from 52 to 1 or vice versa.
I’m also struggling to get my head around why it works this way… (but in my case it’s late at night after a long day )
Edit: hmm, yeah I just noticed that the crossover from 2020 to 2021 looks a bit dodgy. But, in my first example above, how should Fri Jan 1 actually be classified? Should it still be considered part of 2020 week 53, even though it’s in 2021?
Noticed that too with the year crossover. That would be a good question for @Daniel_O. What would be considered a week at year end? Does a new year actually break it up into separate weeks, so you could potentially have 2 short weeks, or should a Tuesday-Monday that crosses over a year still count as a full week that should be grouped together.
The way it works when it comes to weekly pay is as so.
The driver has a cut off of submitting the loads he did for the week on Monday at noon for the last week.
Tuesday pull the report of what was submitted from last Tuesday to Monday deadline (yesterday).
Pay the driver on Friday for that time. Doesn’t matter if it crosses months or years.
From an accounting standpoint at the end of the year, they will get a summary of what they were paid but this will only encompass what was “earned” that past year. So if they had a paycheck that was part of 2020 and 2021… that would count as 2021. But that isn’t important for the app.
My main goal is to have a report of what they earned for the week and for them to be able to look back and the past weeks to see the summary of what was made.
You guys have been really great at helping me out. This is all new to me so I’m learning so much.
OK, I think this should work. The trick is to subtract the weekday number from each date to get to a common date, but also offset it by 2 days so it fits within the Tuesday through Monday requirement. Then it can be integrated into the formula above to build a unique group. By calculating each date into a common date that’s the same for each group of 7 days, then we can build the group with year and week number that’s the same for each of those 7 days.
@Darren_Murphy I’d be curious if it groups differently for you. I’m still confused by how you got away with only subtracting 1 to get it to group by Tuesday, unless some regional setting is kicking in and it recognizes Monday as the first day of a week for you, but recognizes Sunday as the first day of the week for me. I thought Sunday as the first day of a week was global throughout glide, but maybe I’m wrong and it’s regional based on each device. (Each device does this calculation. It doesn’t happen on glide servers.)
In each column, I’ve highlighted the week that crosses the year boundary so they’re a bit easier to compare.
The red one in the middle is my original. The difference seems to be that mine splits the crossover into 2 short weeks (and gets it wrong ), whereas yours considers it a single week that straddles both years.
It’s curious how Tuesday through Friday skip the first week in 2021, and go straight to week 2. Did you notice that? I think I sortof understand why that happens, and it kinda makes sense, maybe?
@Daniel_O I’ll update the formula in my demo to use Jeff’s version, as I think that works better.
I have no idea what kind of voodoo you have going on with your original formula. As shown below, when I use your same original formula, it breaks up the week on Monday like I would expect, so I have no idea why yours does it on Tuesday.
As for the skipping of the week, I think that makes sense. Fri Jan 1st and Sat Jan 2nd fell on the first week of this year, but were grouped with the last week of the prior year. The next Tuesday fell on the second week of the this year, so the next group week number is 02. It’s just that the final week of the year and first week of the next year actually falls within 2 calendar week numbers.