Does anyone know how to convert a WEEKNUM num...

[Tim_Simpson]

Does anyone know how to convert a WEEKNUM number and year to a date, so as to give the ‘week commencing’ date?

I’ve found expression for Googlesheets and excel but they don’t even begin to work in appsheet.

Thanks!

[Aleksi_Alkio]

I’m afraid we don’t have direct tool for that, but you can create that quite easily by yourself. Add one small table with two columns like [Date] and [Year]. For this table add the date of Monday when the week is 1. Then you can calculate what ever date when week number is known.

[Tim_Simpson]

Thanks Aleksi.

Is there no expression which will yield the week commencing date?

In excel the expression is:

=DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7

where A2 is the year and B2 is the weeknumber. G-sheets has a similar expression.

[Aleksi_Alkio]

Aaa… I thought you were asking an expression that would solve the date directly. You can do the same with something like… DATE(CONCATENATE(“12/29/”,[YEAR]-1)) - WEEKDAY(DATE(CONCATENATE(“1/3/”,[YEAR])))+(7*[Week]). Haven’t tested but it should work.

[Chris_Jeal]

Hi @Aleksi ,

How would I go about displaying this in “dd/mm/yyyy” format?

I’ve tried with wrapping in text() but unsuccessful.

text(DATE(CONCATENATE(“12/29/”,YEAR([Date])-1)) - WEEKDAY(DATE(CONCATENATE(“1/3/”,YEAR([Date]))))+(7*weeknum([Date])),“dd/mm/yyyy”)

Thanks,
Chris.

[Tim_Simpson]

I have absolutely NO IDEA how your brain works but WOW!

Thanks a million.

By the way, are you doing this in your sleep?!

[Aleksi_Alkio]

Sometimes yes Actually… you just need to think how you can achieve the requested functionality step by step.