How can I use Google Spreadsheet to format Date in DD/MM/YYYY format?

Question

First thing's first: my spreadsheet's locale is properly set to UK:

enter image description here

If I paste in the following date:

30/09/2013 21:15

Then click Format > Number > Date time

enter image description here

Nothing changes. I also can't seem to sort by date.

Maybe I'm missing something, but the 30/09/2013 21:15 you're pasting in IS in dd/mm/yyyy. It matches the date time format in the drop down. — phantom42, Jan 27 '14 at 17:56
If I paste in your date after changing the spreadsheet settings it works fine for me. Alternatively try setting the number format of the cell first and then pasting in the data. — zelanix, Jan 28 '14 at 2:23
@phantom42 Google spreadsheets adds seconds if it parses the date properly (note I originally said 'milliseconds', this isn't correct). I've re-tested on the latest Google Drive and pasting then changing the format works now. — mikemaccana, Apr 20 '14 at 21:52

mikemaccana · Accepted Answer · 2014-04-20 13:04:36Z

2

This has now been resolved by Google Spreadsheets. Pasting in the date:

30/09/2013 21:15

And clicking Format > Number > Date time now immediately turns the cell into a date time cell (you can see this as it adds seconds).

enter image description here

answered Apr 20 '14 at 13:04

mikemaccana

1831 gold badge2 silver badges9 bronze badges

add a comment |

1 Answer 1