Return the date of the 1st monday of the relevant week using the relevant year of TODAY()

[Skip2MiLu]

I looking for a column Description expression that will return the following text value for column 1 (i.e. Week 1)

Wk 1: Mon, 2 Jan 2023

It does this by looking at the Column Name number 1 as well as the year of Today() i.e. 2023 and then returns the date of the 1st monday of week 1.

I not sure if you able to return the relevant column name dynamically within Appsheet, but I do not mind "hard coding" the week numbers for each column expression (i.e. 1, 2, 3....)
Also factor in that I am using an ISO week numbering type 
ISO week date - Wikipediar

[Skip2MiLu]

I not sure if this helps but below is how I can achieve something similar in excel, but I am not sure how to achieve the same thing in Appsheet.
I would of liked that I column the column name is the week variable in the expression so that I do not need to hardcode the week number for each column description expression.
However, I am not sure if Appsheet has an option for returning the column name as variable?
I also do not mind hard coding the week number if I do not have this option. 

For Column [2] i.e. Week 2 will return the following text value Wk 2: Mon, 09 Jan 2023
"Wk " & (2) & ": " & TEXT(DATE(YEAR(TODAY()), 1, 1) + MOD(9 - WEEKDAY(DATE(YEAR(TODAY()), 1, 1)), 7) + 7 * (2 - 1), "ddd, DD MMM YYYY")

For Column [3] i.e. Week 3 will return the following text value Wk 3: Mon, 16 Jan 2023
"Wk " & (3) & ": " & TEXT(DATE(YEAR(TODAY()), 1, 1) + MOD(9 - WEEKDAY(DATE(YEAR(TODAY()), 1, 1)), 7) + 7 * (3- 1), "ddd, DD MMM YYYY")

[Skip2MiLu]

Hi @Suvrutt_Gurjar 

You help quite a bit on similar expression in the past on the link below.
https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Weeks-Days-and-Total-Days-between-two-dates/td-... 

Are perhaps able to support me on trying to figure out the above expression.

Or does anyone else have an idea?

[Suvrutt_Gurjar]

Hi @Skip2MiLu ,

If understanding of your requirement is correct, I believe it is somewhat complex requirement to be solved with existing AppSheet functions. This is so because you are also looking for ISOWEEK to be respected. AppSheet does have an ISOWEEK() function but it returns the ISO week based on date. And then you may need to repeat the expression for 52 or 53weeks. I believe some ISO years have 52 weeks and some 53. So that number of weeks calculation may also need to be brought in picture as you have column each for 53 weeks. And then the expressions needs to be manually repeated for 53 weeks because AppSheet cannot pull in column name in the expression.

I believe the calculation may be possible but with some longish expression(s).

If I come up with some useful expression, I will respond back. I am sure someone else in the community also could have better ideas and suggestions for you.

[Aurelien]

If that helps, there is a ISOWEEKNUM() - AppSheet Help function 🙂

[Skip2MiLu]

I thought it would be tricky that is why I need to ask the experts! 😉

Would you say this excel formula that I have might also not be factoring ISO weeks correctly (eg: the 52 or 53 week scenario)

For Column [3] i.e. Week 3 will return the following text value Wk 3: Mon, 16 Jan 2023
"Wk " & (3) & ": " & TEXT(DATE(YEAR(TODAY()), 1, 1) + MOD(9 - WEEKDAY(DATE(YEAR(TODAY()), 1, 1)), 7) + 7 * (3- 1), "ddd, DD MMM YYYY")

Also take a look at the below picture on what I am trying to achieve. 

I am also not too concerned, for now, if the expression does accurately calculate the 1st monday date of the relevant week for that year 100% correct in terms of ISOWEEKNUM scenario you mentioned (i.e 52 or 53 week).
As at least the Week number is still shown, and the users has previous week trends to know which week he is on a given date to fill in the form.

[Suvrutt_Gurjar]

Please try below. Not very elegant but I believe it will work. I believe it may respect ISO week numbers as well as basically the expression for  [Week_1_Decider] given below tests what date a first Monday of an ISO year week1  falls on,

Please create a VC called say [Week_1_Decider] with an expression something like below

IFS(WEEKDAY(DATE("12/29/"&YEAR(TODAY())-1))=2,DATE("12/29/"&YEAR(TODAY())-1) ,
WEEKDAY(DATE("12/30/"&YEAR(TODAY())-1))=2,DATE("12/30/"&YEAR(TODAY())-1),
WEEKDAY(DATE("12/31/"&YEAR(TODAY())-1))=2,DATE("12/31/"&YEAR(TODAY())-1),
WEEKDAY(DATE("01/01/"&YEAR(TODAY())))=2,DATE("01/01/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/02/"&YEAR(TODAY())))=2,DATE("01/02/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/03/"&YEAR(TODAY())))=2,DATE("01/03/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/04/"&YEAR(TODAY())))=2,DATE("01/04/"&YEAR(TODAY()))

)

The above expression is for mm/dd/yyyy date system.

If you are using dd/mm/yyyy date system, please try an expression something like 

IFS(WEEKDAY(DATE("29/12/"&YEAR(TODAY())-1))=2,DATE("29/12/"&YEAR(TODAY())-1) ,
WEEKDAY(DATE("30/12/"&YEAR(TODAY())-1))=2,DATE("20/12/"&YEAR(TODAY())-1),
WEEKDAY(DATE("31/12/"&YEAR(TODAY())-1))=2,DATE("31/12/"&YEAR(TODAY())-1),
WEEKDAY(DATE("01/01/"&YEAR(TODAY())))=2,DATE("01/01/"&YEAR(TODAY())) ,
WEEKDAY(DATE("02/01/"&YEAR(TODAY())))=2,DATE("02/01/"&YEAR(TODAY())) ,
WEEKDAY(DATE("03/01/"&YEAR(TODAY())))=2,DATE("03/01/"&YEAR(TODAY())) ,
WEEKDAY(DATE("04/01/"&YEAR(TODAY())))=2,DATE("04/01/"&YEAR(TODAY()))

)

Then the week "1" column expression can be something like 

CONCATENATE("Week 1- ", TEXT([Week_1_Decider], "DDD DD MMM YYYY"))

Then the week "2" column expression can be something like 

CONCATENATE("Week 2- ", TEXT([Week_1_Decider] +7, "DDD DD MMM YYYY"))

Week 3 expression can be 

CONCATENATE("Week 3- ", TEXT([Week_1_Decider] +14, "DDD DD MMM YYYY"))

Week 4 expression can be

CONCATENATE("Week 4- ", TEXT([Week_1_Decider] +21, "DDD DD MMM YYYY"))

.In similar fashion rest of the weeks' expressions

Week 52 expression can be 

CONCATENATE("Week 52- ", TEXT([Week_1_Decider] +51*7, "DDD DD MMM YYYY"))

Week 53 expression can be 

CONCATENATE("Week 53- ", TEXT([Week_1_Decider] +52*7, "DDD DD MMM YYYY"))

Please test well for different calendar years.

[Skip2MiLu]

Thanks, but am I missing something in my expressions.

Week_1_Decider Virtual column expression

Week 1 Description Expression

[Suvrutt_Gurjar]

Sorry, I am unable to decipher the screenshots you shared, especially the second one on mobile. I am unable to properly see the expressions etc. 

I am in travel till next mid week. So you could please try to post very specific screenshots and describe the issue in text with larger description.

Or else I will respond when I am back to my work deak.

[Skip2MiLu]

Hi, Let me know if this helps. 

So, I created a virtual column called. 
Week_1_Decider (Date column)

IFS
	(
  WEEKDAY(DATE("29/12/"&YEAR(TODAY())-1))=2,DATE("29/12/"&YEAR(TODAY())-1),
  WEEKDAY(DATE("30/12/"&YEAR(TODAY())-1))=2,DATE("20/12/"&YEAR(TODAY())-1),
  WEEKDAY(DATE("31/12/"&YEAR(TODAY())-1))=2,DATE("31/12/"&YEAR(TODAY())-1),
  WEEKDAY(DATE("01/01/"&YEAR(TODAY())))=2,DATE("01/01/"&YEAR(TODAY())),
  WEEKDAY(DATE("02/01/"&YEAR(TODAY())))=2,DATE("02/01/"&YEAR(TODAY())),
  WEEKDAY(DATE("03/01/"&YEAR(TODAY())))=2,DATE("03/01/"&YEAR(TODAY())),
  WEEKDAY(DATE("04/01/"&YEAR(TODAY())))=2,DATE("04/01/"&YEAR(TODAY()))
  )

Then I have used the following Description expression for the Week 1 column 

CONCATENATE("Week 1: ", TEXT([Week_1_Decider], "DDD, DD MMM YYYY"))

It is only returning Week 1: 
Instead of Week 1: Mon, 02 Jan 2023

What am I missing?

[Suvrutt_Gurjar]

Thank you. Could you update what is the column type for the "1:, "2" ....."53" columns?

Hope it is text type.

[Skip2MiLu]

Columns 1 to 53 are a Decimal type that the user indicates a quantity per that relevant week.

Why would the column type have influence on s Description expression? I would of thought that the column type would only have an influence on expression for that column where it is referring from or returning values to that column. Eg: expression within initial value, valid, suggested values etc of that column but that a column description expression would always be text.

Not sure what I am missing. 

[Suvrutt_Gurjar]

Oh okay, I thought you are using it as an app formula. I missed thepoint that you are using it in descriptipn.

Please try 

CONCATENATE("Week 1- ", TEXT( 

IFS(WEEKDAY(DATE("12/29/"&YEAR(TODAY())-1))=2,DATE("12/29/"&YEAR(TODAY())-1) ,
WEEKDAY(DATE("12/30/"&YEAR(TODAY())-1))=2,DATE("12/30/"&YEAR(TODAY())-1),
WEEKDAY(DATE("12/31/"&YEAR(TODAY())-1))=2,DATE("12/31/"&YEAR(TODAY())-1),
WEEKDAY(DATE("01/01/"&YEAR(TODAY())))=2,DATE("01/01/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/02/"&YEAR(TODAY())))=2,DATE("01/02/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/03/"&YEAR(TODAY())))=2,DATE("01/03/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/04/"&YEAR(TODAY())))=2,DATE("01/04/"&YEAR(TODAY()))

)

, "DDD DD MMM YYYY"))

[Skip2MiLu]

OK. I'm I correct in my understanding that I am not required to use a
virtual column and this Column Description expression can be used in all
columns (I. E. From 1,2,3...up to 53) without modify each expression for each column specifically?

[Suvrutt_Gurjar]

You will of course need to modify the expression for each column number. The sample given is for column 1. I have basically substituted vc [Week_1_Decider] expression by the long expression I had suggested for it. . So making a different expression for each column is still required

 Please simply substitute the vc [Week_1_Decider]  with the long expression.

[Skip2MiLu]

Apologies for the additional question.

Which part of the equation (I. E. Week number variable) will I need to
adjust in the expression.

So for example, how will the expression look like for column [2] (I. E week
2)

Also on a side note is there a function like CONTEX Column name within
AppSheet. So that I could perhaps consider making this expression dynamic
vs hardcoding 53x different expressions

[Suvrutt_Gurjar]

For week 2 it will be

CONCATENATE("Week 2- ", TEXT( 

IFS(WEEKDAY(DATE("12/29/"&YEAR(TODAY())-1))=2,DATE("12/29/"&YEAR(TODAY())-1) ,
WEEKDAY(DATE("12/30/"&YEAR(TODAY())-1))=2,DATE("12/30/"&YEAR(TODAY())-1),
WEEKDAY(DATE("12/31/"&YEAR(TODAY())-1))=2,DATE("12/31/"&YEAR(TODAY())-1),
WEEKDAY(DATE("01/01/"&YEAR(TODAY())))=2,DATE("01/01/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/02/"&YEAR(TODAY())))=2,DATE("01/02/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/03/"&YEAR(TODAY())))=2,DATE("01/03/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/04/"&YEAR(TODAY())))=2,DATE("01/04/"&YEAR(TODAY()))

) +7

, "DDD DD MMM YYYY"))

For week 3 it will be

CONCATENATE("Week 3- ", TEXT( 

IFS(WEEKDAY(DATE("12/29/"&YEAR(TODAY())-1))=2,DATE("12/29/"&YEAR(TODAY())-1) ,
WEEKDAY(DATE("12/30/"&YEAR(TODAY())-1))=2,DATE("12/30/"&YEAR(TODAY())-1),
WEEKDAY(DATE("12/31/"&YEAR(TODAY())-1))=2,DATE("12/31/"&YEAR(TODAY())-1),
WEEKDAY(DATE("01/01/"&YEAR(TODAY())))=2,DATE("01/01/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/02/"&YEAR(TODAY())))=2,DATE("01/02/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/03/"&YEAR(TODAY())))=2,DATE("01/03/"&YEAR(TODAY())) ,
WEEKDAY(DATE("01/04/"&YEAR(TODAY())))=2,DATE("01/04/"&YEAR(TODAY()))

) +14

, "DDD DD MMM YYYY"))

You could build for other weeks based on these examples.

As mentioned at the beginning, AppSheet does not have any function currently to pull iñ the column name. You may need to build the expressions manually.

There could be other convoluted ways like building a read only table of 53 rows etc. But all these approaches could still be extensive. 

[Suvrutt_Gurjar]

Note: If you are okay with using the expressions in 'Display Name" setting of the column rather than "Description" setting, then even the following expressions will work.

Then the week "1" column expression can be something like 

CONCATENATE("Week 1- ", TEXT([Week_1_Decider], "DDD DD MMM YYYY"))

Then the week "2" column expression can be something like 

CONCATENATE("Week 2- ", TEXT([Week_1_Decider] +7, "DDD DD MMM YYYY"))

Week 3 expression can be 

CONCATENATE("Week 3- ", TEXT([Week_1_Decider] +14, "DDD DD MMM YYYY"))

Week 4 expression can be

CONCATENATE("Week 4- ", TEXT([Week_1_Decider] +21, "DDD DD MMM YYYY"))

.In similar fashion rest of the weeks' expressions

Week 52 expression can be 

CONCATENATE("Week 52- ", TEXT([Week_1_Decider] +51*7, "DDD DD MMM YYYY"))

Week 53 expression can be 

CONCATENATE("Week 53- ", TEXT([Week_1_Decider] +52*7, "DDD DD MMM YYYY"))