Date-Formatting in TEXT formula independent from used language in Excel

by

If the only problem is sharing it with your Polish co-worker, then you can simply use the international placeholder “e” to replace “YYYY”:

=TEXT(A1,"e-MM-DD")

Now, while this would work fine for your co-worker in Poland, there are countries where “m” and “d” would also need replacement. If in general we need to return the locale “Y”, “M” or “D” equivalent you could create three named formulas using the name manager:

  • 1st: Create name called YT and refer to =INDEX(GET.WORKSPACE(37),19)
  • 2nd: Create name called MT and refer to =INDEX(GET.WORKSPACE(37),20)
  • 3rd: Create name called DT and refer to =INDEX(GET.WORKSPACE(37),21)

Now you can use a reference to these names through:

=TEXT(A1,REPT(YT,4)&"-"&REPT(MT,2)&"-"&REPT(DT,2))

NOTE: Depending on which version of Excel you use it could be necessary to:
Enable Excel 4.0 macros when VBA macros are enabled in the Trust Center

More Related Contents:

  1. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  2. How to convert to percentage [closed]
  3. Excel Number Separation [closed]
  4. I have an Excel data sheet that I would like to extract data from into a new sheet
  5. Getting unique values in Excel by using formulas only
  6. Return empty cell from formula in Excel
  7. Excel: last character/string match in a string
  8. Referencing value in a closed Excel workbook using INDIRECT?
  9. Simple Pivot Table to Count Unique Values
  10. How can I perform a reverse string search in Excel without using VBA?
  11. Excel UNIQUE Across Columns
  12. Filling any empty cells with the value above
  13. How to create a string or formula containing double quotes in Excel?
  14. Find last used cell in Excel VBA
  15. Remove leading or trailing spaces in an entire column of data
  16. Excel Date to String conversion
  17. Excel: Formulas for converting data among column / row / matrix
  18. Excel: Searching for multiple terms in a cell
  19. Three Dimensional Lookup Using INDEX/MATCH
  20. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  21. Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
  22. Excel: Search for a list of strings within a particular string using array formulas?
  23. Convert from US to UK format
  24. excel vlookup with multiple results
  25. How to roundup a number to the closest ten?
  26. Convert an Array Formula’s Text Results into a Usable Format
  27. Can’t pass a range to a lambda using Let and Makearray functions
  28. Check whether a cell contains a substring
  29. Unique Rank value for a subgroup within a group
  30. Get content of a cell given the row and column numbers

Leave a Comment