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...
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.
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.
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.
- 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.
- Thanks Patrick. Will have another look at it.