Excel Magic Trick 1457 Part 2: Regional Settings & Text or Number Date / Times in SUMIFS Function




About this tutorial:

Video duration: 3:21
Download File:
Entire page with all Excel Files for All Videos:
In the English United States Regional Settings, SUMIFS seems to be recognizing Text and Number Dates as equivalent Criteria. But when we change the Reginal Settings, SUMIFS correctly interprets Text Dates and Number Dates as different. This Tip comes from Awesome Online Teammate, Bill Szysz!!!

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

10 thoughts on “Excel Magic Trick 1457 Part 2: Regional Settings & Text or Number Date / Times in SUMIFS Function

    PLAN XLSM

    (September 17, 2019 - 3:50 am)

    pmsocho

    (September 17, 2019 - 3:50 am)

    It is worth to add that the operating system version also matters!
    For over 20 year the default short date format in Windows in Poland was "yyyy-mm-dd" and with the launch of Windows 10 Microsoft changed that to "dd.mm.yyyy". Many users who don't understand how Date&Time works get confused.
    I have 2 videos on that topic waiting in the queue but it looks like you can read someone's mind 🙂
    How did you read what's in my head? 🙂 🙂 🙂

    Bill Szysz

    (September 17, 2019 - 3:50 am)

    Thanks, Mike 🙂
    One additional note: If you have dates (as text) in an international form (like this one ="2010-01-10" or this one '2010-01-10) then some functions read it as a date and this behavior is independent on regional settings.

    Leila Gharani

    (September 17, 2019 - 3:50 am)

    Thank you Mike for posting and Bill for pointing this out. These regional settings can be really tricky sometimes! @ Bill: my next video is about you too – you need to start doing English videos 🙂

    Enny Kraft

    (September 17, 2019 - 3:50 am)

    Lots of people in the US aren't aware of the fact that they are the only major country in the world who solely use MM/DD/YYYY (the other ones being the Federated States of Micronesia and the Marshall Islands). Take a look at https://en.wikipedia.org/wiki/Date_format_by_country to see how dates are formatted around the world. It's one big headache when working for international companies.
    Your example worked fine in German (DD.MM.YYY) but only because your date was 1/1/2010. If you change it to 12/23/2010 is no longer does for most countries since they spell dates DD/MM/YYYY. The other day I gave an Excel class in a large international company and their OS and Office languages were all over the place. Some people had a US or GB English OS and their Office was German or vice versa. When they entered the date they were used to but which didn't match their system settings, lots of formulas didn't work anymore. Whether you use a dot or a slash as a separator doesn't matter btw.
    When you open a file from another country and language, not only will Excel translate all functions, it will also convert the date formatting – as long as it was entered correctly on the other computer. But this won't work when you send .txt or .csv files since their dates are all text. So do your colleagues abroad a favor when doing a database export and send them .xlsx files instead.

    John Borg

    (September 17, 2019 - 3:50 am)

    Thanks very Interesting !!!

    Syed Muzammil Mahasan Shahi

    (September 17, 2019 - 3:50 am)

    Another great video. Thanks Mike and Bill Syzs

    Mohamed Chakroun

    (September 17, 2019 - 3:50 am)

    The Team is stongest by having a very good Player like Bill Syzs

    Vida

    (September 17, 2019 - 3:50 am)

    Knowing that Excel cannot recognize different formats, cleaning data should be the first step to do (format all dates as dates, etc.) before creating formulas.

    Vlat Komora

    (September 17, 2019 - 3:50 am)

    TIL you can do Super+E(open explorer) Alt+D(go to address bar) "Control PanelAll control panel itemsRegion and Language"

Leave a Reply

Your email address will not be published. Required fields are marked *