# Calculating This Week in GDE **URL:** \ **Category:** Ask for Help\ **Created:** [November 28, 2020, 8:02pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075 "2020-11-28T20:02:42Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Errcomp](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/errcomp/32/13195_2.png) [@Errcomp](https://community.glideapps.com/u/Errcomp)\ **Post date:** [November 28, 2020, 8:02pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/1 "2020-11-28T20:02:42Z")
I’m working on marking rows “This Week”. I realized I would need to first convert the Now date into a serial number date. Here’s the algorithm for converting a date’s month, day, year into to serial number: Trunc(( 1461 \* ( nYear + 4800 + Trunc(( nMonth - 14 ) / 12) ) ) / 4) + Trunc(( 367 \* ( nMonth - 2 - 12 \* ( ( nMonth - 14 ) / 12 ) ) ) / 12) - Trunc(( 3 \* ( Trunc(( nYear + 4900 + Trunc(( nMonth - 14 ) / 12) ) / 100) ) ) / 4) + nDay - 2415019 - 32075 Source: [Excel Serial Date to Day, Month, Year and Vice Versa - CodeProject](https://www.codeproject.com/Articles/2750/Excel-Serial-Date-to-Day-Month-Year-and-Vice-Versa) Next I am going to use this to calculate what is this week: > Will let you all know how it goes. Has anyone attempted this before? Any tips? ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 9:33pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/2 "2020-11-28T21:33:26Z")
There are functions in Google that can do this very easily for you. ---
**Author:** ![Errcomp](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/errcomp/32/13195_2.png) [@Errcomp](https://community.glideapps.com/u/Errcomp)\ **Post date:** [November 28, 2020, 9:34pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/3 "2020-11-28T21:34:45Z")
My client is not liking the delay when using google sheets formulas. Its about 7 seconds. ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 9:35pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/4 "2020-11-28T21:35:36Z")
I see…well…I will get back to you on that one then 🙂 ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 9:46pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/5 "2020-11-28T21:46:08Z")
so using the WEEKNUM formula and matching the results took too long? Or did you use a different way? ---
**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:** [November 28, 2020, 10:22pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/6 "2020-11-28T22:22:46Z")
You just need a boolean to tell you if your row’s timestamp is this week or not, correct? ---
**Author:** ![Errcomp](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/errcomp/32/13195_2.png) [@Errcomp](https://community.glideapps.com/u/Errcomp)\ **Post date:** [November 28, 2020, 10:23pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/7 "2020-11-28T22:23:49Z")
Using Weeknum to label the entry as ThisWeek for the rollup made the rollup update after 7 seconds. More problematic is Today() is in that formula, and sometimes it doesn’t update, so trying to avoid it. ![E31F33884B7A43D4B9B92838D347A35B.png](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/1X/677f06bdf711468707faf594c72c7993f90d2513.png) ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 11:02pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/8 "2020-11-28T23:02:57Z")
do you have the worksheet open while you are testing the time. It creates a larger delay while its open. ---
**Author:** ![Errcomp](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/errcomp/32/13195_2.png) [@Errcomp](https://community.glideapps.com/u/Errcomp)\ **Post date:** [November 28, 2020, 11:04pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/9 "2020-11-28T23:04:17Z")
Today() doesn’t update in Glide always, so that’s the bigger problem I’m hoping to work around. ---
**Author:** ![Errcomp](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/errcomp/32/13195_2.png) [@Errcomp](https://community.glideapps.com/u/Errcomp)\ **Post date:** [November 28, 2020, 11:05pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/10 "2020-11-28T23:05:48Z")
Hmm, that first formula didn’t seem to work. It was off by several days. This ended up working: > ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 11:06pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/11 "2020-11-28T23:06:06Z")
There are some workarounds with google sheets, Glide will update every edit, and when the sheets are closed and the google servers are taking the work load its around a 2 second delay. With this being said you can utilize a onChange script to force the edit and force the 2 second update. ---
**Author:** ![Errcomp](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/errcomp/32/13195_2.png) [@Errcomp](https://community.glideapps.com/u/Errcomp)\ **Post date:** [November 28, 2020, 11:10pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/12 "2020-11-28T23:10:06Z")
I need the Today () function to update at midnight though. That’s not always happening. ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 11:14pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/13 "2020-11-28T23:14:09Z")
run a timer script that just types a number 1 in a cell you dont use. it will force the edit update. So set timer for 12am and cell a1 on sheetnoonesheardoforuses is value 1. I can write the script out if you tell me sheet name and cell. ---
**Author:** ![Errcomp](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/errcomp/32/13195_2.png) [@Errcomp](https://community.glideapps.com/u/Errcomp)\ **Post date:** [November 28, 2020, 11:15pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/14 "2020-11-28T23:15:49Z")
I read that Glide wont update when scripts make changes. Have you tried it? Does it work? ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 11:16pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/15 "2020-11-28T23:16:34Z")
yes, it works. the scripts wont update if used as a onedit script, but a onchange script will. I use them like they aregoing out of style.Literally every app I make uses them. Glide isnt robust enough to do everything just yet so I make scripts even write formulas when onchanges occur. ---
**Author:** ![Errcomp](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/errcomp/32/13195_2.png) [@Errcomp](https://community.glideapps.com/u/Errcomp)\ **Post date:** [November 28, 2020, 11:17pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/16 "2020-11-28T23:17:35Z")
But you are talking about a time based trigger change. This isn’t onedit or onchange. Have you used that? ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 11:18pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/17 "2020-11-28T23:18:12Z")
right, since you only need it at midnight then it will make the change on its own and glide will post the change.Glide doesn’t know the difference between you making the change and a script. Google knows the difference between glide making a change and a formula making a change and us making a change. ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 11:20pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/18 "2020-11-28T23:20:39Z")
Also a quick and easy fix is to just change google updates to every minute and turn on iterative calculations. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/2/f2423a0218e5ec90ea8a102dd26b87795bfccf05.png) ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/b/fbdb715828da505c1b59a858fa5a581fbd5b0d5c.png) ---
**Author:** ![Errcomp](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/errcomp/32/13195_2.png) [@Errcomp](https://community.glideapps.com/u/Errcomp)\ **Post date:** [November 28, 2020, 11:39pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/19 "2020-11-28T23:39:01Z")
It is currently set to every minute. I may try the script! In the mean time, was able to do it completely in GDE (there is one arrayformula in Date Ref to get a base date of 1899-12-30 - will test if that slows it down still). ![Glide community - screenshot 1](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/7/37fd0b922f4a37e615be2566b282747665535c33.png) ![Glide community - screenshot 2](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/3/33bcc11cbdfd69bd4960eb2d905971c153a2d9a9.png) ![Glide community - screenshot 3](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/d/c/dcf7709c34274c7b6fdf35066ebc69eabde2cd7a.png) ![Glide community - screenshot 4](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/9/7/9768704e4efc656ef05cd305edb6cb2588f74773.png) ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 28, 2020, 11:43pm UTC](https://community.glideapps.com/t/calculating-this-week-in-gde/19075/20 "2020-11-28T23:43:32Z")
Thats great! 😁 Keep us updated. [Next page](https://community.glideapps.com/t/calculating-this-week-in-gde/19075.md?page=2)