change date format m/d/yyyy to dd/mm/yyyy using fo

Question

  sr  date       date-text  mm  dd  yyyy    concat(dd,mm,yyyy)
  1   12/31/2018 12/31/2018 12  31  2018    31/12/2018
  2   3/31/2019  3/31/2019  3/  1/  2019    1//3//2019

as shown above i have some dates in col 'date' formatted as m/d/yyyy. i need to convert this to dd/mm/yyyy. i tried DATEVALUE funtion but it gives value error as my pc date format is dd/mm/yyyy. Unfortunately i cant change my PC date format. So i tried to split the date using LEFT,MID and RIGHT function.

date-text = TEXT([@[date]],"mmm/ddd/yyyy") - to fix date format to mm/dd/yyyy
mm =LEFT([@[date-text]],2)
dd =MID([@[date-text]],4,2)
yyyy=RIGHT([@[date-text]],4)
concat(dd,mm,yyyy) = =CONCAT([@dd],"/",[@mm],"/",[@yyyy]) to get mm/dd/yyyy for mat.

This approach works for row 1 but for row 2 i get wrong date "1//3//2019". please let me know how to fix this

Answer 1

You can get the result you want with just one formula:

=DATE(VALUE(RIGHT(A2;4));VALUE(MID(A2;1;FIND("/";A2;1)-1));VALUE(MID(A2;FIND("/";A2;1)+1;FIND("/";A2;FIND("/";A2;1)+1)-FIND("/";A2;1)-1)))

I understand very well your situation. I'm from Portugal and here we have the same date format as you (dd/mm/yyyy). Pay attention to your pasted values as a "date". For instance, if you place 3/31/2019 Excel won't recognize it as a date and paste it as text (that's why you don't need your date-text column). However, if you paste 1/12/2019 Excel will write automatically 01/12/2019 when the true date is 12/01/2019 and the formula will not work.

Answer 2

Try following formula

=DATE(RIGHT(A1,4),LEFT(A1,SEARCH("/",A1)-1),TRIM(MID(LEFT(A1,SEARCH("/",A1,SEARCH("/",A1)+1)-1),SEARCH("/",A1)+1,2)))