logo elektroda
logo elektroda
X
logo elektroda

[Solved] Excel replacing a comma with a period in a number changes the format to a date

andy_ja 10326 6
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
  • #1 17841264
    andy_ja
    Level 2  
    As the topic
    I have the number 1.3
    I replace the comma with a period and instead of the expected 1.3 I have the date
    Does anyone know why
    Office 365 Excel 1808 Build 10730.20280
    Polish language, Windows display enabled
    Polish programmer's keyboard
    decimal symbol of a number = ", '
  • ADVERTISEMENT
  • #2 17841272
    viayner
    Level 43  
    Hello,
    regional settings - check what and how is defined.
    greetings
  • ADVERTISEMENT
  • #3 17841275
    atomic99
    Level 34  
    Change the cell format to text and it should be OK
  • ADVERTISEMENT
  • Helpful post
    #4 17841701
    Maciej Gonet
    VBA, Excel specialist
    The creators of Excel are obsessed with replacing anything that might resemble a date with that date. And unfortunately I don't know how to turn it off. The key here is the date format set in the operating system in the regional settings, because Excel reads the default date format from it. In Windows 10 PL, the date format with dots dd.mm.yyyy was adopted as the default (previously it was with dashes) and this causes that the 1.3 entry is treated as March 1. It seems that the only solution is to change the default date format in the operating system to one with dashes, possibly with slashes (but then it will replace 1-2 or 1/2 with dates, i.e. 1/2 and so it replaces it, because it is a standard American, which Excel itself recognizes).
  • #5 17842265
    paweliw
    IT specialist
    As he wrote Maciej Gonet the basis in Windows 10 is to change the date format in the regional settings of the operating system.
    The best for the one with dashes and this applies to both the short and long dates, without that there will be constant problems with the date format in Excel :)
  • #6 17843073
    andy_ja
    Level 2  
    and exactly - M. Gonet's post helped precisely. I changed the date format from dot to "/" and it worked (it stopped changing to dates). I even untied another one from the same barrel. And such a general reflection. Transferring excel data between different countries - must create such confusion. We have a comma, in GB - there is a dot in the US "/" etc. There is no good solution.
    Thanks to everyone and best regards.
  • ADVERTISEMENT
  • #7 17843077
    andy_ja
    Level 2  
    and exactly - M. Gonet's post helped precisely. I changed the date format from dot to "/" and it worked (stopped changing to dates). I even untied another one from the same barrel. And such a general reflection. Transferring excel data between different countries - must create such confusion. We have a comma, in GB - there is a dot in the US "/" etc. There is no good solution.
    Thanks to everyone and best regards.

    Added after 1 [minutes]:

    I followed M.Gonet's advice and it's OK

Topic summary

The discussion revolves around an issue in Excel where replacing a comma with a period in a number (e.g., 1.3) inadvertently converts it into a date format (March 1) due to regional settings. Users suggest checking and modifying the regional settings in Windows 10, specifically changing the default date format from dots (dd.mm.yyyy) to slashes (dd/mm/yyyy) or dashes (dd-mm-yyyy) to prevent Excel from misinterpreting numeric entries as dates. The problem highlights the confusion arising from different decimal and date formats across countries, particularly when transferring Excel data internationally.
Summary generated by the language model.
ADVERTISEMENT