Recognizing Dates in Apple Numbers.

Question

Level 1

44 points

Recognizing Dates in Apple Numbers.

I download my account information from my Credit Union, and for more than a decade I was using Excel. The big problem that I face is that the format that I am forced to download it in is CSV, but the credit union pust their date format like this: 01Jun2022 with no spaces, and I can't seem to find a way to parse that such quickly so that numbers can recognize it as a date and format it, and sort it as a date. Numbers only sees it as text. Is there a solution, or do I have to keep renting MS-Excel for the rest of my life?

Posted on Jun 4, 2022 7:19 PM

Reply

Answer 1

SGIII

Level 8

37,274 points

Jun 4, 2022 8:12 PM in response to grburgess

grburgess wrote:

date format like this: 01Jun2022 with no spaces, and I can't seem to find a way to parse that such quickly so that numbers

You can do something like this:

In a column next to the original dates, here in B2 with the downloaded dates starting in A2:

=DATEVALUE(LEFT(A2,2)&"-"&MID(A2,3,3)&"-"&RIGHT(A2,4))

Fill this down the column.

If your region uses , as the decimal separator then use this instead:

=DATEVALUE(LEFT(A2;2)&"-"&MID(A2;3;3)&"-"&RIGHT(A2;4))

This takes the original and converts it to 01-Jun-2022 then applies DATEVALUE to that.

After you've converted the dates you can "remove" the formula by selecting the column, typing command-c to copy, followed by Edit > Paste Formula Results. Then delete the original date column if you want.

SG

Reply

Answer 2

grburgess Author

Level 1

44 points

Jun 4, 2022 8:44 PM in response to SGIII

Yes, I had just done that, but I used more than one cell to think it through, but that's exactly what I did. Thanks for this because being able to have the formula in just 1 cell is even better.

Reply

Recognizing Dates in Apple Numbers.

Similar questions