0

What I need is a formula which provides me with the date of the start of a week (Monday is day 1) for any date i chose. for example 20/10/2019 (Sunday) should yield 14/10/2019 as the start of the week. Previous similar posts don't prove to solve my problem because i have a date and not the week number (i tried this). How do I convert a calendar week into a date in Excel?

I used the date to get the week number in order to make the posted solutions work. Unfortunately excel's WEEKNUM formula automatically assumes Sunday as the first day of the week and so each Sunday's week number is counted as the following week.

WEEKNUM counts Sunday as following week

For 20/10/2019 the start of the week needs to be 14/10/2019

Wayne
  • 3
  • 1

1 Answers1

1

Try 

=GivenDate-WEEKDAY(GivenDate,2)+1

replace GivenDate with a valid date or the cell reference of a valid date, such as 20/10/2019 which shall return 14/10/2019.

Terry W
  • 3,199
  • 2
  • 8
  • 24
  • @Wayne welcome to StackOverflow. By the way I think you should not limit your search within this community as I am sure there are many tutorials online that covered this topic either directly or indirectly. If you give a try of all functions mentioned in the links you provided in your post, you may figure it out yourself eventually. If you think my answer is working for you just fine, please consider marking it as the solution by clicking the **tick mark** under down vote button next to my answer :) – Terry W Oct 15 '19 at 04:19