Week Number math issue

I spoke a little too soon, on this @Darren_Murphy . What I want to be able to do is use the week number column as the “now” so that it is confirming for me that the week number calculated by Weeknum(now) corresponds to the date of this week. How would I modify your formula to do that? My attempts result in a number rather than a date.
Continuing the discussion from Convert WeekNum to "Week of XXX":

So you have a table where you store logs of something, and you want to confirm if the logs are falling into “this week” or not?

um, if that’s the case why not just do Weeknum(Now), and then do a direct comparison? :man_shrugging:

1 Like

@ThinhDinh, yes basically. And fair point @Darren_Murphy …I’m realizing that I didn’t totally explain the set up in the first post.

So I have a variable (“X”) that increments the week number up or down:

Weeknum(Now)+X. As I or other admins click the button, I want it to confirm the week using the calendar date, so if you were going up, each time you clicked the button it would go:

Week of June 5th, 2023
Week of June 12th, 2023


Edit: Meanwhile the Weeknum(Now)+X column is being used in a query of documents that have different dates and displaying them in a collection based on the week that they were created. So each time one clicks the button, the document list changes. A version of this list is then emailed to the client, so the goal is to make sure you’re emailing documents for the right week.

Does that start at zero for now, or are you calculating the actual week number of the year based on the current date?

If I’m understanding your use case correctly, I think I’d be more inclined to work with raw dates rather than messing around trying to convert between dates and week numbers. So instead of incrementing by 1, increment by 7 each time you click the button.

I see your thinking. But the dates of the documents may be one of seven dates within a given week and I’m trying to group documents by week. So in Table 1, I was using WeekNum(date) to see first which week the documents were created in. And then in Table 2 I have a column “whichWeek” which is the math column with the WeekNum(Now)+X formula. And then in the query I’m asking "Show me all the documents created in the week that meets the Which Week value. "

But so the button is changing the “whichWeek” column value but I want that to display on the front in not as “23” but “week of June 5”

You can calculate the first day of the current week… then you will get all the first days using Darren’s suggestion… this technique is better because it will give you all years… not just the current one.


Oh I see. Ok. Now that I get it, I like that approach. Thanks @Darren_Murphy and @Structure_Art_DIGITA.

1 Like

I agree with the others here… get the current date and subtract the day of week to get a consistent value for the first day of the current week.


That will push him back to the previous week :wink:
You must adjust it by 1… if your week starts on Monday.


Then add adjustment for the week you need x 7

1 Like

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.