Avatar of easycapital
easycapital
Flag for Macao asked on

What date is this weeks Monday

Dear experts,

For week number: A1 = 15
The date to be displayed in A2 for the corresponding Monday that given week.
Note that A1 can be changed and A2 is to update via a formula.

Thanks!
JP
Microsoft OfficeMicrosoft ExcelSpreadsheets

Avatar of undefined
Last Comment
easycapital

8/22/2022 - Mon
Shums Faruk

Try below formula in A2 assuming its for 2017:
=(DATE(2017,1,-2)-WEEKDAY(DATE(2017,1,3))+A1*7)-WEEKDAY(DATE(2017,1,-2-WEEKDAY(DATE(2017,1,3))+A1*7-2))

Open in new window

SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Shums Faruk

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

On what basis is the WEEKNUM being calculated? From Help file for the function:

Return_type      Week begins on      System
1 or omitted      Sunday                  1
2                  Monday                  1
11                  Monday                  1
12                  Tuesday                  1
13                  Wednesday            1
14                  Thursday            1
15                  Friday                  1
16                  Saturday                  1
17      Sunday      1
21      Monday      2

Once you know the basis of the WEEKNUM calculation you can use the FLOOR or CEILING functions to round the date to the beginning or end of the week and then add or subtract the relevant number of days to get to the Monday of the week.
ASKER CERTIFIED SOLUTION
Rob Henson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

See attached file with basic option with fixed values in A1 and A2.

Also more flexible options in rows 14 and 15, enter year in B14 and use dropdown or enter day in C14.

Thanks
Rob
Day-of-week.xlsx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Shums Faruk

Corrected formula for getting Monday's Date without linking so many cells and assuming current year for specified week in A1:
=(DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+A1*7)-WEEKDAY(DATE(YEAR(TODAY()),1,-2-WEEKDAY(DATE(YEAR(TODAY()),1,1))+A1*7-2))

Open in new window

Rob Henson

I have chosen my suggestion as I feel the logic I have used is simpler to understand.
easycapital

ASKER
Dear Rob,
Thanks for the solution.
Could you please make the week start on Monday instead of Sunday?
Br,
JP
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.