CODEWITHSUNDEEP
google-sheets
Kunal Shah
Kunal Shah

Reputation: 21

pasting dd/mm/yyyy dates in google sheets

In a text file I have date column as dd/mm/yyyy but when I am pasting the same as it is (paste as values) to google sheets, google sheets converts that to mm/dd/yyyy. That means, 07/06/2017 becomes 06 July and 22/12/2017 becomes invalid dates. I am having to manually change that.

What is the way to force google sheets to stop auto converting dd/mm/yyyy to mm/dd/yyyy ?

Thanks Kunal

Upvotes: 2

Views: 2838

Answers (3)

Alexander Caravitis
Alexander Caravitis

Reputation: 11

Assuming your US format date is in cell A2, this formula will fix it to European (UK) date format:

=datevalue(text(left(right(A2;len(A2)-find("/";A2));find("/";right(A2;len(A2)-find("/";A2)))-1)&"/"&left(A2;find("/";A2)-1)&"/"&right(right(A2;len(A2)-find("/";A2));len(right(A2;len(A2)-find("/";A2)))-find("/";right(A2;len(A2)-find("/";A2))));"dd/mm/yyyy"))

The above formula works even if some dates or months have a trailing zero and others do not, and will add a trailing zero in the final result if needed, that's why it's so long :)

Upvotes: 1

Max Makhrov
Max Makhrov

Reputation: 18707

In some regions the default date format is: dd/mm/yyyy, eg Russia. Try changing:

  1. File → Spreadsheet Settings...
  2. Change locale

Upvotes: 3

aliyasineser
aliyasineser

Reputation: 196

You can edit the cell. Choose the cell(or cells). There is a format box and "more formats" button. More Formats -> more formats -> more date and time formats... from that, you can choose or create a pattern for your project.

Upvotes: 0

Related Questions