# Showing pay Monday-Sunday **URL:** \ **Category:** Ask for Help\ **Created:** [February 14, 2021, 4:26am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751 "2021-02-14T04:26:00Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Daniel\_O](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/daniel_o/32/21282_2.png) [@Daniel\_O](https://community.glideapps.com/u/Daniel_O)\ **Post date:** [February 14, 2021, 4:26am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/1 "2021-02-14T04:26:00Z")
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. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 14, 2021, 4:43am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/2 "2021-02-14T04:43:03Z")
_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. > [@Fun with Dates](https://community.glideapps.com/t/fun-with-dates/21684): > > I just remembered something from a long time ago. I don’t remember if I had posted about it in this forum or in the old spectrum forum. Can’t find the post in either forum right now. Working on these posts re-triggered this memory. What I had discovered back then is how ridiculously forgiving the calendar layout is. It can take dates in a variety of formats and still determine the correct date to show. But the really cool thing I had discovered is that you can feed it certain types of te… ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [February 14, 2021, 5:09am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/3 "2021-02-14T05:09:08Z")
Do you want to calculate them for each day, or should it be the full week? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 14, 2021, 5:12am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/4 "2021-02-14T05:12:05Z")
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). ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [February 14, 2021, 5:14am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/5 "2021-02-14T05:14:16Z")
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. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 14, 2021, 5:20am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/6 "2021-02-14T05:20:05Z")
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 🤣 ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [February 14, 2021, 5:24am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/7 "2021-02-14T05:24:03Z")
@ThinhDinh got it. Was just writing out the same formula. `(YEAR(Date)*100)+WEEKNUM(Date)` ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 14, 2021, 6:54am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/8 "2021-02-14T06:54:33Z")
@Daniel_O - I’ve put together a demo to show how this can be done. > **[Glide Community Forum](https://glide-community.glideapp.io/)** 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. ---
**Author:** ![Daniel\_O](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/daniel_o/32/21282_2.png) [@Daniel\_O](https://community.glideapps.com/u/Daniel_O)\ **Post date:** [February 14, 2021, 3:15pm UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/9 "2021-02-14T15:15:41Z")
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](https://support.google.com/docs/answer/3294949?hl=en) ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 14, 2021, 3:50pm UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/10 "2021-02-14T15:50:58Z")
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: ```auto (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? ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [February 14, 2021, 3:59pm UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/11 "2021-02-14T15:59:25Z")
@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. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 14, 2021, 4:06pm UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/12 "2021-02-14T16:06:15Z")
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) ![Screen Shot 2021-02-15 at 12.01.06 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/9/7/97d84d3fb167005265928247972b54ac5a719f15.png) Wednesday (-2) ![Screen Shot 2021-02-15 at 12.01.44 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/c/5c66a0aada3ef84f5f2005f95402414aa25acbdf.png) Thursday (-3) ![Screen Shot 2021-02-15 at 12.02.08 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/7/37db1122d27da999479f36dfc539f24b3aa8a9b3.png) 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? ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [February 14, 2021, 4:13pm UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/13 "2021-02-14T16:13:36Z")
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. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 14, 2021, 4:15pm UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/14 "2021-02-14T16:15:24Z")
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? ---
**Author:** ![Daniel\_O](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/daniel_o/32/21282_2.png) [@Daniel\_O](https://community.glideapps.com/u/Daniel_O)\ **Post date:** [February 14, 2021, 4:27pm UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/15 "2021-02-14T16:27:40Z")
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. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 14, 2021, 4:40pm UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/16 "2021-02-14T16:40:48Z")
Okay, so in this example (with a new week starting on Tuesday)… ![Screen Shot 2021-02-15 at 12.01.06 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/9/7/97d84d3fb167005265928247972b54ac5a719f15.png) 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? ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [February 14, 2021, 8:25pm UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/17 "2021-02-14T20:25:48Z")
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.) ```auto (YEAR(Date-WEEKDAY(Date-2))*100) + WEEKNUM(Date-WEEKDAY(Date-2)) ``` ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/9/39bcbd4abec8b6d3adde6f2160000b78d327be84.png) ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 15, 2021, 2:35am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/18 "2021-02-15T02:35:59Z")
> [@Jeff\_Hager](#): > > @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.) 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. ![Screen Shot 2021-02-15 at 10.18.35 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/4/34e599c1ad64a94f0c157e7d01a3873e62d429a9.png) - 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. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [February 15, 2021, 5:14am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/19 "2021-02-15T05:14:10Z")
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. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/d/fdb92155e68542063f346e42887f0b8e6ff4d3bc.png) ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/8/38a2af7457b47b4ad1743e502d54bdd3f852ea93.png) ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 15, 2021, 5:37am UTC](https://community.glideapps.com/t/showing-pay-monday-sunday/22751/20 "2021-02-15T05:37:00Z")
> [@Jeff\_Hager](#): > > I have no idea what kind of voodoo you have going on with your original formula. 🤣 🤣 🤣 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. > [@Jeff\_Hager](#): > > 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. yah, agreed. I more or less understood that, but wasn’t able to find a way to elucidate it. [Next page](https://community.glideapps.com/t/showing-pay-monday-sunday/22751.md?page=2)