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

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!

0 6 977
6 REPLIES 6

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.

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.

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.

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.

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

Thanks a million.

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

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

Top Labels in this Space