Thursday, October 30, 2014

yogi_Convert Date in MM/DD/YYYY Style to DD/MM/YYYY Style

                 Google Spreadsheet   Post  #1810
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Oct-30-2014
post by  tidoko:
Formula to change day, month, year order
Hi Folks,

I currently have a list of dates: 10/23/2014 (month/day/year). I'd like to change it to be (day/month/year), is there a formula to do so? And I tried to edit the cell date format, but the date is a result from an existing formula, so it does not do anything. 
Thanks Yogi, do you work for google? I see your answers here often, and they're quite good. The formula you offered "TEXT(<date>,"dd/mm/yyyy")" works but it does not switch the day and month. Is seems to simply convert the existing cell into text. 

I'm using the older google sheets if that matters. The cell which I'd like to switch the date order on is polled by the following formula

(1) =arrayformula(if(LEN(mbnaRAW!A$2:A)>0;mbnaRAW!A$2:A;""))

(2) This results in the following cell date "10/23/2014"

(3) When trying to shift the day and month via "=TEXT(<date>,"dd/mm/yyyy")" is swill shows "10/23/2014"