Convert from US to UK format

by

use:

=TEXTJOIN("/",,FILTERXML("<a><b>"&SUBSTITUTE(LEFT(TEXT(A1,"d/m/yy")&" ",FIND(" ",TEXT(A1,"d/m/yy")&" ")-1),"/","</b><b>")&"</b></a>","//b["&{2,1,3}&"]"))+MID(TEXT(A1,"\ hh:mm AM/PM"),FIND(" ",TEXT(A1,"\ hh:mm AM/PM"))+1,99)

and then format the output as desired.

![enter image description here

Note: I am US based so I had to reverse it in the demo.

Another Note: If one is US based dealing with UK dates then change the d/m/yy to m/d/yy and it will work.

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. Excel VBA – How to add dynamic array formula
  6. Getting unique values in Excel by using formulas only
  7. Return empty cell from formula in Excel
  8. Excel: last character/string match in a string
  9. Referencing value in a closed Excel workbook using INDIRECT?
  10. Simple Pivot Table to Count Unique Values
  11. Difference between date and time w/out work week Excel
  12. How can I perform a reverse string search in Excel without using VBA?
  13. Excel UNIQUE Across Columns
  14. Filling any empty cells with the value above
  15. How to create a string or formula containing double quotes in Excel?
  16. Find last used cell in Excel VBA
  17. Remove leading or trailing spaces in an entire column of data
  18. Excel Date to String conversion
  19. Excel: Formulas for converting data among column / row / matrix
  20. Excel: Searching for multiple terms in a cell
  21. Three Dimensional Lookup Using INDEX/MATCH
  22. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  23. How to list all possible combinations of the values in three columns in excel?
  24. Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
  25. How to split texts from dynamic range?
  26. Excel: Search for a list of strings within a particular string using array formulas?
  27. How to roundup a number to the closest ten?
  28. Convert an Array Formula’s Text Results into a Usable Format
  29. Can’t pass a range to a lambda using Let and Makearray functions
  30. Unique Rank value for a subgroup within a group

Leave a Comment