Windows 10 Date Format Breaking Formula

[Timberwolf]

Good morning All

I recently bought a new laptop. I have a excel sheet that checks for a companies name in a table then checks the date to see if the date is in the range I'm looking for eg. 2019. When I opened my table on my new computer my formulas all returned "0" instead of the values they should have. After messing around for awhile I gave up. In the middle of the night I had a ah ha moment and realized it was the new comps windows date format messing things up (old format was 31/01/2019, mm/dd/yyyy new date format was 01-31-2019, dd-mm-yyyy) because of this it was changing the date in my table to that format while the formulas were still looking for the old format.

Ok so I changed the windows date format and everything works great again WOOHOO. Then my boss opens the file on his comp and his windows date format is 01/31/2019 dd/mm/yyyy and it breaks again....

There has to be a work around or a different way to type my formula so it works on all date formats (not everyone uses 1 date format)

Please help, here is my formula.

=COUNTIFS('All Years Table'!E$2:E$100103,[@[Vendor Names]],'All Years Table'!G$2:G$100103,">=01/01/2019",'All Years Table'!G$2:G$100103,"<=13/12/2019")

 

[RoryA]

You can use the DATE function:

=COUNTIFS('All Years Table'!E$2:E$100103,[@[Vendor Names]],'All Years Table'!G$2:G$100103,">="&DATE(2019,1,1),'All Years Table'!G$2:G$100103,"<="&DATE(2019,12,13))

 

[Timberwolf]

Thank you for your quick response. Could you please explain what the difference is? I have a ton of formulas that rely on the format that I used. I will change them to the new format but I would like to understand what the difference is besides the fact that my formula is garbage lol. I'm self taught by just stumbling around in the dark until what I try works lol

 

[RoryA]

The DATE function takes three number arguments - year, month and day. There is therefore no reliance on regional settings, so it will produce the same result whether your settings are mm/dd/yyyy or dd/mm/yyyy.

 

[Timberwolf]

Thank you very much! I will give it a try and let you know how it goes.

 

[Timberwolf]

It worked great! Thank you very much. Now just to go around changing it in all the other excel sheet I've made..... lol