Forum Discussion
Help with excel date formula
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 | |||||
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.
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- 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.
