How do I find the date of the first day of week given any date and assuming Monday as day 1 in Excel

0
votes

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

1
exceldatedayofweekweek-number

1 Answers

1
votes

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.