Excel dates are now separated by comma instead of slash; how do I get the slashes back?

Question

After upgrading to Windows 10 (I believe) using MS Office 2015; all the dates in my excel spreadsheets are now separated by commas.

i.e. 15th of August used to look like 15/08/2017 and is now 15,08,2017

I have changed the formatting in Control Panel and my dates appear with slashes perfectly fine everywhere else on my laptop except for MS Excel.

This is so annoying!

s

Answer 1

Application.International(xlDateSeparator)                 ,

Here's the issue.

Do as follows:

Compare / Check the Language settings the control panel (your keyboard layout should be "English, US"):

Click "Change date, time, or number formats":

Click "Additional settings..."

Click the Reset button.

Click Ok on all window

Reboot

Open Excel and check the behavior

Problem solved?

Andreas.

Answer 2

Check this option.

Andreas.

Answer 3

Check this option.

That option is checked, looks just like it does in the picture you posted; do you mean to say uncheck it?

s

Answer 4

That option is checked, looks just like it does in the picture you posted; do you mean to say uncheck it?

No, when I say "check" means check. ;-) Do as follows:

Close Excel

Open Excel

Right-Click a sheet tab and choose "View code"

Copy & Paste in the code below

Place the cursor anywhere inside the code

Press F5 to run the macro

Open the Immediate window (Press CTRL-G)

Press CTRL-A to select all contents, press CTRL-C to copy

Paste the contents here

Andreas.

Sub ApplicationInternational()

  Dim Value, Names, Descr, i As Integer

'!!F-

  Value = Array( _

    "Eckige und geschweifte Klammern", xlLeftBrace, xlLeftBracket, xlLowerCaseColumnLetter, xlLowerCaseRowLetter, xlRightBrace, xlRightBracket, xlUpperCaseColumnLetter, xlUpperCaseRowLetter, _

    "Landes-/Regionseinstellungen", xlCountryCode, xlCountrySetting, xlGeneralFormatName, _

    "Währung", xlCurrencyBefore, xlCurrencyCode, xlCurrencyDigits, xlCurrencyLeadingZeros, xlCurrencyMinusSign, xlCurrencyNegative, xlCurrencySpaceBefore, xlCurrencyTrailingZeros, xlNoncurrencyDigits, _

    "Datum und Uhrzeit", xl24HourClock, xl4DigitYears, xlDateOrder, xlDateSeparator, xlDayCode, xlDayLeadingZero, xlHourCode, xlMDY, xlMinuteCode, xlMonthCode, xlMonthLeadingZero, xlMonthNameChars, xlSecondCode, xlTimeSeparator, xlTimeLeadingZero, xlWeekdayNameChars, xlYearCode, _

    "Maßeinheitensysteme", xlMetric, xlNonEnglishFunctions, _

    "Trennzeichen", xlAlternateArraySeparator, xlColumnSeparator, xlDecimalSeparator, xlListSeparator, xlRowSeparator, xlThousandsSeparator)

  Names = Array( _

    "Eckige und geschweifte Klammern", "xlLeftBrace", "xlLeftBracket", "xlLowerCaseColumnLetter", "xlLowerCaseRowLetter", "xlRightBrace", "xlRightBracket", "xlUpperCaseColumnLetter", "xlUpperCaseRowLetter", _

    "Landes-/Regionseinstellungen", "xlCountryCode", "xlCountrySetting", "xlGeneralFormatName", _

    "Währung", "xlCurrencyBefore", "xlCurrencyCode", "xlCurrencyDigits", "xlCurrencyLeadingZeros", "xlCurrencyMinusSign", "xlCurrencyNegative", "xlCurrencySpaceBefore", "xlCurrencyTrailingZeros", "xlNoncurrencyDigits", _

    "Datum und Uhrzeit", "xl24HourClock", "xl4DigitYears", "xlDateOrder", "xlDateSeparator", "xlDayCode", "xlDayLeadingZero", "xlHourCode", "xlMDY", "xlMinuteCode", "xlMonthCode", "xlMonthLeadingZero", "xlMonthNameChars", "xlSecondCode", "xlTimeSeparator", "xlTimeLeadingZero", "xlWeekdayNameChars", "xlYearCode", _

    "Maßeinheitensysteme", "xlMetric", "xlNonEnglishFunctions", _

    "Trennzeichen", "xlAlternateArraySeparator", "xlColumnSeparator", "xlDecimalSeparator", "xlListSeparator", "xlRowSeparator", "xlThousandsSeparator")

'!!F+

  For i = LBound(Value) To UBound(Value)

    If IsNumeric(Value(i)) Then

      Debug.Print "Application.International(" & Names(i) & ")"; Tab(60); Application.International(Value(i))

    Else

      Debug.Print

      Debug.Print Value(i)

    End If

  Next

End Sub

Answer 5

Application.International(xl4DigitYears)                   False

Application.International(xlDateOrder)                      1

Application.International(xlDateSeparator)                 ,

Application.International(xlDayCode)                       d

Application.International(xlDayLeadingZero)                True

Application.International(xlHourCode)                      h

Application.International(xlMDY)                           False

Application.International(xlMinuteCode)                    m

Application.International(xlMonthCode)                     m

Application.International(xlMonthLeadingZero)              True

Application.International(xlMonthNameChars)                 3

Application.International(xlSecondCode)                    s

Application.International(xlTimeSeparator)                 :

Application.International(xlTimeLeadingZero)               False

Application.International(xlWeekdayNameChars)               3

Application.International(xlYearCode)                      y

Maßeinheitensysteme

Application.International(xlMetric)                        True

Application.International(xlNonEnglishFunctions)           False

Trennzeichen

Application.International(xlAlternateArraySeparator)       @

Application.International(xlColumnSeparator)               ,

Application.International(xlDecimalSeparator)              .

Application.International(xlListSeparator)                 ,

Application.International(xlRowSeparator)                  ;

Application.International(xlThousandsSeparator)            ,

Eckige und geschweifte Klammern

Application.International(xlLeftBrace)                     {

Application.International(xlLeftBracket)                   [

Application.International(xlLowerCaseColumnLetter)         c

Application.International(xlLowerCaseRowLetter)            r

Application.International(xlRightBrace)                    }

Application.International(xlRightBracket)                  ]

Application.International(xlUpperCaseColumnLetter)         C

Application.International(xlUpperCaseRowLetter)            R

Landes-/Regionseinstellungen

Application.International(xlCountryCode)                    1

Application.International(xlCountrySetting)                 61

Application.International(xlGeneralFormatName)             General

Währung

Application.International(xlCurrencyBefore)                True

Application.International(xlCurrencyCode)                  $

Application.International(xlCurrencyDigits)                 2

Application.International(xlCurrencyLeadingZeros)          True

Application.International(xlCurrencyMinusSign)             True

Application.International(xlCurrencyNegative)               1

Application.International(xlCurrencySpaceBefore)           False

Application.International(xlCurrencyTrailingZeros)         True

Application.International(xlNoncurrencyDigits)              2

Datum und Uhrzeit

Application.International(xl24HourClock)                   False

Application.International(xl4DigitYears)                   False

Application.International(xlDateOrder)                      1

Application.International(xlDateSeparator)                 ,

Application.International(xlDayCode)                       d

Application.International(xlDayLeadingZero)                True

Application.International(xlHourCode)                      h

Application.International(xlMDY)                           False

Application.International(xlMinuteCode)                    m

Application.International(xlMonthCode)                     m

Application.International(xlMonthLeadingZero)              True

Application.International(xlMonthNameChars)                 3

Application.International(xlSecondCode)                    s

Application.International(xlTimeSeparator)                 :

Application.International(xlTimeLeadingZero)               False

Application.International(xlWeekdayNameChars)               3

Application.International(xlYearCode)                      y

Maßeinheitensysteme

Application.International(xlMetric)                        True

Application.International(xlNonEnglishFunctions)           False

Trennzeichen

Application.International(xlAlternateArraySeparator)       @

Application.International(xlColumnSeparator)               ,

Application.International(xlDecimalSeparator)              .

Application.International(xlListSeparator)                 ,

Application.International(xlRowSeparator)                  ;

Application.International(xlThousandsSeparator)            ,

Eckige und geschweifte Klammern

Application.International(xlLeftBrace)                     {

Application.International(xlLeftBracket)                   [

Application.International(xlLowerCaseColumnLetter)         c

Application.International(xlLowerCaseRowLetter)            r

Application.International(xlRightBrace)                    }

Application.International(xlRightBracket)                  ]

Application.International(xlUpperCaseColumnLetter)         C

Application.International(xlUpperCaseRowLetter)            R

Landes-/Regionseinstellungen

Application.International(xlCountryCode)                    1

Application.International(xlCountrySetting)                 61

Application.International(xlGeneralFormatName)             General

Währung

Application.International(xlCurrencyBefore)                True

Application.International(xlCurrencyCode)                  $

Application.International(xlCurrencyDigits)                 2

Application.International(xlCurrencyLeadingZeros)          True

Application.International(xlCurrencyMinusSign)             True

Application.International(xlCurrencyNegative)               1

Application.International(xlCurrencySpaceBefore)           False

Application.International(xlCurrencyTrailingZeros)         True

Application.International(xlNoncurrencyDigits)              2

Datum und Uhrzeit

Application.International(xl24HourClock)                   False

Application.International(xl4DigitYears)                   False

Application.International(xlDateOrder)                      1

Application.International(xlDateSeparator)                 ,

Application.International(xlDayCode)                       d

Application.International(xlDayLeadingZero)                True

Application.International(xlHourCode)                      h

Application.International(xlMDY)                           False

Application.International(xlMinuteCode)                    m

Application.International(xlMonthCode)                     m

Application.International(xlMonthLeadingZero)              True

Application.International(xlMonthNameChars)                 3

Application.International(xlSecondCode)                    s

Application.International(xlTimeSeparator)                 :

Application.International(xlTimeLeadingZero)               False

Application.International(xlWeekdayNameChars)               3

Application.International(xlYearCode)                      y

Maßeinheitensysteme

Application.International(xlMetric)                        True

Application.International(xlNonEnglishFunctions)           False

Trennzeichen

Application.International(xlAlternateArraySeparator)       @

Application.International(xlColumnSeparator)               ,

Application.International(xlDecimalSeparator)              .

Application.International(xlListSeparator)                 ,

Application.International(xlRowSeparator)                  ;

Application.International(xlThousandsSeparator)            ,

Eckige und geschweifte Klammern

Application.International(xlLeftBrace)                     {

Application.International(xlLeftBracket)                   [

Application.International(xlLowerCaseColumnLetter)         c

Application.International(xlLowerCaseRowLetter)            r

Application.International(xlRightBrace)                    }

Application.International(xlRightBracket)                  ]

Application.International(xlUpperCaseColumnLetter)         C

Application.International(xlUpperCaseRowLetter)            R

Landes-/Regionseinstellungen

Application.International(xlCountryCode)                    1

Application.International(xlCountrySetting)                 61

Application.International(xlGeneralFormatName)             General

Währung

Application.International(xlCurrencyBefore)                True

Application.International(xlCurrencyCode)                  $

Application.International(xlCurrencyDigits)                 2

Application.International(xlCurrencyLeadingZeros)          True

Application.International(xlCurrencyMinusSign)             True

Application.International(xlCurrencyNegative)               1

Application.International(xlCurrencySpaceBefore)           False

Application.International(xlCurrencyTrailingZeros)         True

Application.International(xlNoncurrencyDigits)              2

Datum und Uhrzeit

Application.International(xl24HourClock)                   False

Application.International(xl4DigitYears)                   False

Application.International(xlDateOrder)                      1

Application.International(xlDateSeparator)                 ,

Application.International(xlDayCode)                       d

Application.International(xlDayLeadingZero)                True

Application.International(xlHourCode)                      h

Application.International(xlMDY)                           False

Application.International(xlMinuteCode)                    m

Application.International(xlMonthCode)                     m

Application.International(xlMonthLeadingZero)              True

Application.International(xlMonthNameChars)                 3

Application.International(xlSecondCode)                    s

Application.International(xlTimeSeparator)                 :

Application.International(xlTimeLeadingZero)               False

Application.International(xlWeekdayNameChars)               3

Application.International(xlYearCode)                      y

Maßeinheitensysteme

Application.International(xlMetric)                        True

Application.International(xlNonEnglishFunctions)           False

Trennzeichen

Application.International(xlAlternateArraySeparator)       @

Application.International(xlColumnSeparator)               ,

Application.International(xlDecimalSeparator)              .

Application.International(xlListSeparator)                 ,

Application.International(xlRowSeparator)                  ;

Application.International(xlThousandsSeparator)            ,

s