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

expressions
(Tim Simpson) #1

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) #2

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) #3

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) #4

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.

(Tim Simpson) #5

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) #6

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