Help with excel date formula

[Mr_Raj_C]

Dear Excel Community,

Hope you are all keeping well ?

I was hoping someone could assist me with the below conundrum / formula that i'm trying to implement (without success) :(

Essentially, i would like a formula to return the date at the beginning of the week in the cell next to "W/c=" based on the date input in the cell under the "Inspection date". So for this week, if the inspection date is today (08/12/22), i would like Excel to return the date for Monday of that week (05/12/22).

I researched online and found the following formula =E8-WEEKDAY(E8,2)+1 which doesn't seem to work. I would then like an easy way to copy this formula and roll it out for all the other cell's were "W/c=" is required.

I hope you can help,

Thanks

Raj

December
W/c =	7/11/2022	W/c =	#VALUE!	W/c =		W/c =
Inspection Date	Inspection By	Inspection Date	Inspection By	Inspection Date	Inspection By	Inspection Date	Inspection By
7/12/2022	Team leader	14/12/22

[#2]

@Mr_Raj_C 

Try this one (Presuming an Inspection date is in A2):

=A2-CHOOSE(WEEKDAY(A2,2),0,1,2,3,4,5,6)

Sample workbook attached.

[#3]

@Patrick2788 

Hi Patrick,

Thank you for your prompt reply and sample workbook. The strange thing is, when i take your formula and transpose it on to my workbook, i get strange results.

So when i entered the inspection date as 08/12/22. I should get 05/12/22. However in my workbook, it returns 08/08/22 ?

It works fine in your sample book.

I've attached my workbook.

Any ideas ?

Thanks

Raj

[#4]

The formula is good. It looks like there's a mix of locales.

In my region, the date format commonly used is m/d/yyy. Your locale seems to use d/m/yyy.

You'll want to re-enter your inspection dates and then format accordingly for d/m/yyy.

[#5]

@Patrick2788 Thank you for trying.

I had a hunch it might be something to do with that. 

Surely there must be a formula or a way to make a formula work which accommodates the (d/m/y) format ? It's just not going to be practical for our contractors to enter the date in (m/d/y) as they will surely forget.

[#6]

The formula can accommodate both formats. You have to make sure you're using d/m/yyy throughout your workbook. There were a few m/d/yyy and that's why the calculations were off.

[#7]

Thanks Patrick. Will have another look at it.