0

I am in Australia and use the default date formats here (dd/mm/yyyy). Unfortunately, BandCamp exports sales data using a US date format: "m/d/yy h:mmpm". For the most part, Numbers treats this as text since most of the values don't make sense as dates when reading in the local format.

Is there a way to convert this text to an actual date value in Numbers?

The only function I have found that seems close is DATEVALUE, but it interprets the source data as d/m/yy, and so returns an error except where the dates make sense reversed. e.g. it returns an error for 1/18/19, and interprets 2/4/19 as 2 April 2019, when the source data means 4 February 2019.

2 Answers 2

1

I had the same problem importing a .csv file into Numbers. The date is a string in the format ‘m/d/y h:mmpm’ where m has a length of 1 or 2, d has a length of 1 or 2, and y is a 2 digit year number. Here is the formula that I developed to change this to a date value. It should work for any date with a year in the 2000s (the value is in cell A1):

DATE("20" & MID($A1,FIND("/",$A1,3)+1,2),LEFT($A1,FIND("/",$A1,1)−1),MID($A1,FIND("/",$A1,1)+1,FIND("/",$A1,3)−1−FIND("/",$A1,1)))

1
  1. Set the documents Language & Region setting to "Englisch | United States of America" in the respective dialog (File menu > Advanced > Language & Region).

Formatting options for date cells in the Format sidebar should have US formatting now.

  1. Import/paste the date data.

  2. After importing/pasting, set the Language & Region setting (back) to "English | Australia". The pasted/imported cells with "US" dates will retain US date formatting. All other cells have AUS date formatting, so using DATEVALUE on the US formatted cells "converts" them to AUS date formatting.

Hope this makes sense, not an English native speaker.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .