Calculating This Week in GDE

[Errcomp]

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

Next I am going to use this to calculate what is this week:

See if a date is in same or previous calendar week

Will let you all know how it goes. Has anyone attempted this before? Any tips?

[Drearystate]

There are functions in Google that can do this very easily for you.

[Errcomp]

My client is not liking the delay when using google sheets formulas. Its about 7 seconds.

[Drearystate]

I see…well…I will get back to you on that one then

[Drearystate]

so using the WEEKNUM formula and matching the results took too long? Or did you use a different way?

[ThinhDinh]

You just need a boolean to tell you if your row’s timestamp is this week or not, correct?

[Errcomp]

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.

[Drearystate]

do you have the worksheet open while you are testing the time. It creates a larger delay while its open.

[Errcomp]

Today() doesn’t update in Glide always, so that’s the bigger problem I’m hoping to work around.

[Errcomp]

Hmm, that first formula didn’t seem to work. It was off by several days.

This ended up working:

Formula to convert date to number

[Drearystate]

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.

[Errcomp]

I need the Today () function to update at midnight though. That’s not always happening.

[Drearystate]

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.

[Errcomp]

I read that Glide wont update when scripts make changes. Have you tried it? Does it work?

[Drearystate]

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.

[Errcomp]

But you are talking about a time based trigger change. This isn’t onedit or onchange. Have you used that?

[Drearystate]

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.

[Drearystate]

Also a quick and easy fix is to just change google updates to every minute and turn on iterative calculations.

[Errcomp]

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).

[Drearystate]

Thats great! Keep us updated.