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.
cough @Jeff_Hager cough 
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.
Do you want to calculate them for each day, or should it be the full week?
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).
Yeah, the week number can be duplicated across the years. A thought to make it unique is either combine it via a template, let’s say “2021 - Week 1” or via a math column, Year * 100 + Weeknum.
oooh, Weeknum() is a valid Glide math function. I didn’t know that! (undocumented?)
That makes it much easier…
Edit. I see Jeff typing. I’ll shut up now 
@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.
This is amazing! Thank you so much @Darren_Murphy. How could you tweak the formula if you need to do a different range? Let’s say Tuesday-Monday…
(YEAR(Date)100)+WEEKNUM(Date*, 12**)
Am I correct in adding the [type] after the date in the WEEKNUM()? If Im reading this right WEEKNUM - Docs Editors Help
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:
(YEAR(Date-1)*100)+WEEKNUM(Date-1)
To start the week on Wednesday, use Date-2
Thursday, Date-3
etc…
@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.
Yeah, I agree that subtracting seems a bit counter-intuitive, but I was testing just now and that’s what seems to work…
Tuesday (-1)
Wednesday (-2)
Thursday (-3)
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.
I might have to play around with this for awhile.
As an aside, have you noticed that when editing the formula in a math column, the cursor jumps to the end of the line with every keypress?
Or am I the only one that’s inflicted with that?
The way it works when it comes to weekly pay is as so.
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.
Okay, so in this example (with a new week starting on Tuesday)…
What should we do with Fri 1st through Monday 4th?
Should they be:
a) a continuation of the last week of 2020, or
b) the first week (a short, 4 day week) of 2021?
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.)
(YEAR(Date-WEEKDAY(Date-2))*100)
+
WEEKNUM(Date-WEEKDAY(Date-2))
It appears to be the same. I can’t explain why my first version “worked” (Sunday is also the first day of the week for me).
Just for giggles, I did it for every day of the week using your updated formula.
), whereas yours considers it a single week that straddles both years.
@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.
I’ve checked, and I’ve double-checked. The two formulas are identical, but you and I get different results. It MUST be some kind of regional variation. That’s all I can think of that makes any sense.
yah, agreed. I more or less understood that, but wasn’t able to find a way to elucidate it.
