Windows 10 Date Format Breaking Formula

Timberwolf

New Member
Joined
Feb 20, 2018
Messages
26
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")
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
It worked great! Thank you very much. Now just to go around changing it in all the other excel sheet I've made..... lol
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top