How to properly format dates in Google Sheets or Microsoft Excel

PMO1948 0
Viewed 61

I have a spreadsheet I need to make in Google Sheets. The source of some of the data is exported to an Excel sheet. The data arrives in a dd/mm/yyyy format and I need to display it in a MON d format (Ex Sep 5).

The problem is both excel and sheets look at the date that arrives and think it is mm/dd/yyyy.

For example, 02/08/2022 is believed to be Febuary 8 even though it should be Aug 2. The problem then arises that neither of these platforms end up knowing how to convert this to Aug 2 and I end up having to do this manually.

Does anyone know how to get around this?

I have tried adjusting the format of the date, as well as using DateValue to convert (this fails since it understands the date as mm/dd/yyyy even when it is dd/mm/yyyy).

Any leads would be appreciated!

Thanks!

date google-sheets excel
3 Answers

In Google Sheets, choose File > Settings > Locale and select a locale that uses the dd/mm/yyyy date format, before importing the data. You can then format the date column the way you prefer.

doubleunary0

in gs:

=TEXT(REGEXREPLACE(A1&""; "(\d+)\/(\d+)\/(\d+)"; "$1/$1/$3"); "mmm d")

enter image description here

player00

Try the following and format the result to your liking

=INDEX(IF(ISNUMBER(U2:U5),U2:U5,
           IF(U2:U5=DATEVALUE("1899-12-30"),, 
               (MID(U2:U5,4,3)&LEFT(U2:U5,3)&RIGHT(U2:U5,4))*1)))

(Do adjust the formula according to your ranges and locale)

enter image description here

Functions used:

marikamitsos0
Related