Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Hi,
pls try
Regards
pls try
=DATE(YEAR(TODAY()),1,A1*7-(DAY(DATE(YEAR(TODAY()),1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),1,1),2)))-1)-7
or
=DATE(YEAR(TODAY()),1,A1*7-(DAY(DATE(YEAR(TODAY()),1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),1,1),2)))-1)
Regards
I would advice to have Year in B1, so you can change any Week Number in A1 or Year in B1, you will have First Monday of the week in A2, just to cross check, if date is correct, I have formula in B2 to show the day of the resulted date.
Please see attached....
Date-for-Monday-as-per-WeekNumber.xlsx
Please see attached....
Date-for-Monday-as-per-WeekNumber.xlsx
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.
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.
Ignore the above comment, reading between the lines you are wanting the nth Monday of the year, with n being set in A1. Therefore, try this:
=IF(WEEKDAY(DATE(2017,1,1),1)<=2,FLOOR(DATE(2017,1,1),7)+2,CEILING(DATE(2017,1,1),7)+2)+(A1-1)*7
This assumes that you want for year 2017, to make more flexible change the 2017 values to a cell reference containing the year.
The formula calculates on the following:
If the first day of the year is Monday or prior round back to the previous Saturday and add 2 days, if the first day of the year is Tuesday or beyond round up to the following Saturday and add 2 days. Each of these will give the first Monday of the year. Then adding A1 (weeknumber value) multiplied by 7 will give the nth Monday of the year. The weeknumber is reduced by 1 as you already have the first Monday so you only need n-1 weeks later.
=IF(WEEKDAY(DATE(2017,1,1),1)<=2,FLOOR(DATE(2017,1,1),7)+2,CEILING(DATE(2017,1,1),7)+2)+(A1-1)*7
This assumes that you want for year 2017, to make more flexible change the 2017 values to a cell reference containing the year.
The formula calculates on the following:
If the first day of the year is Monday or prior round back to the previous Saturday and add 2 days, if the first day of the year is Tuesday or beyond round up to the following Saturday and add 2 days. Each of these will give the first Monday of the year. Then adding A1 (weeknumber value) multiplied by 7 will give the nth Monday of the year. The weeknumber is reduced by 1 as you already have the first Monday so you only need n-1 weeks later.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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
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
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))
Dear Rob,
Thanks for the solution.
Could you please make the week start on Monday instead of Sunday?
Br,
JP
Thanks for the solution.
Could you please make the week start on Monday instead of Sunday?
Br,
JP
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Open in new window