calculate the date from given week number

Question

Level 1

8 points

calculate the date from given week number

Hello everyone,

it is easy to calculate the week number out of the dates given. 01/01.2019 (German dd/mm/yyyy) to 05/01/2019 which is week number 1. WEEKNUM(A3,2)

My question is if it is possible to do the opposite.

Get the start and end date of a week from the given week number.

For the week number 8 then the result would be 18.02.2019 to 24.02.1019.

Column 1+2 are simply just Wn 1, Wn 2, Wn 3 etc. Column 3 and possibly 4 should show the start of the week and the end.

week number	wn	date
Wn	1	1. - 5.
Wn	2	8. - 12.
Wn	3	15. - 19.
Wn	4

I have found older examples but they don't fulfil my needs.

Thank you for your help.

limmy3

iMac (27-inch, Late 2013), macOS Mojave (10.14.1)

Posted on Nov 17, 2018 7:34 AM

Reply

Answer 1

Top-ranking reply

Wayne Contello

Level 7

26,589 points

Nov 18, 2018 3:52 AM in response to limmy3

I suggest something like this:

put the week number in column A

In column B, the start day for the week is:

B2=DATE(2018, 1, 1)+"7d"×(A2−1)

this is shorthand for... select cell B2, then type, or copy and paste from here, the formula:

=DATE(2018, 1, 1)+"7d"×(A2−1)

C2=B2+"6d"

to fill down, select cells B2 and C2,

copy

select cells B2 thru the end of column C, paste

Reply

Answer 2

SGIII

Level 8

36,278 points

Nov 18, 2018 3:52 AM in response to limmy3

You can try something like this:

The formula in C2, filled down:

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

The formula in D2, filled down:

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

Or in D2, filled down, just use =C2+4

When I switch to German the formulas are shown as:

In C2, filled down:

=DATUM(A2;1;1)−WOCHENTAG(DATUM(A2;1;1);2)+(B2−1)*7+1

In D2, filled down:

=DATUM(A2;1;1)−WOCHENTAG(DATUM(A2;1;1);2)+B2*7−2

(or just =C2+4)

SG

Reply

Answer 3

limmy3 Author

Level 1

8 points

Nov 21, 2018 1:10 AM in response to limmy3

Thank you Wayne Contello and SGII for your answers and support. Both are very useful and I did use both by now.

So who solved my question? 😕

I am throwing the coin … 😉

Thank you

limmy3

Reply

calculate the date from given week number

Similar questions