0

I have an Excel file with a date column. The first few entries look like this:

enter image description here

I'd like to adjust the date format because the current format is not usable for an application I have in mind in QGIS, a geographic information system. The following link contains information on how to adjust date formats in Excel. Upon following the instructions therein, however, I noticed that Excel does not seem to recognize the correct date of the sample. Here's what I see when I press Cmd+1, go to the Number panel, and to the Date category:

enter image description here

For example, the fourth preview type should read 12/11/2020, not 14/03/2012.

Question: how can I make Excel read in the correct date, and adjust it to another format?

2
  • Or am I now just being silly, and does Excel indeed recognize the correct date, but gives another date in the example types? – Max Muller Nov 26 '20 at 12:55
  • 1
    The date formats in the Type window all relate to 14-Mar-2012. They are NOT preview dates. The preview will show in the Sample window above. But, your dates are text strings and not real dates. Note that they are left-justified on the worksheet; and that the Sample did not change to match the selected formatting. First you have to convert them to "real" Excel dates. There are plenty of examples how to do this on this forum. – Ron Rosenfeld Nov 26 '20 at 14:13
1

I thought you could define a custom date format like mmmm dd, yyyy but that still does not seem to be recognised. You have to convert the text date to a known date format that Excel can recognise: (with date in A1, put this in B1)

=(MID(A1,SEARCH(" ",A1)+1,SEARCH(",",A1)-SEARCH(" ",A1)-1)&" "&LEFT(A1,SEARCH(" ",A1)) & RIGHT(A1,4))+0

Now you can format the date however you like.

1
  • FYI, if you can figure out which country uses that specific date formatting you can easily solve this problem without formulas by simply changing your Region settings in Windows. See my post here superuser.com/questions/1605337/… – Mobus Nov 26 '20 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.