How do I reverse an ascending column of dates in an imported Bank statement.

Hi Rossco,

Rossco65 wrote:

It’s not so much that I want to reverse the order of the date from e.g. 10/05/2014 to 2014/05/10

It’s a bank statement and the order in which It’s been exported has the oldest dates and entries at the bottom of the statement and
the more recent dates and entries at the top.

I want to reverse the order of the dates and entries to read from the bottom to top down.

You have encountered a common problem when importing dates into spreadsheet programs.

From your screenshot it appears Numbers isn't recognizing the values in your 'Date' column as dates (probably because the imported dates are dd/mm/yyyy and your machine is set to expect mm/dd/yyyy). If Numbers recognized them as dates they would be right-aligned in the column, whereas in your screenshot they are left-aligned. Numbers is treating them as text, which means they won't sort as you expect.

So the solution is to reformat the values in that column so that Numbers recognizes them as dates. Reformatting them is what the formula in Barry's solution does. Once they are reformatted, Numbers automatically treats them as dates and you can then get the results you want when you sort on that column.

To reformat them, you can use a formula approach, as Barry has done. This requires setting up a new column and copying the formula through that column, then sorting on that new column, rather than your original imported column.

You can also use a script to reformat the values in situ. That sounds more complicated. But actually (once the script is written, which I have done) that approach is easier for the user because you don't need to set up an extra column and insert formulas. All you have to do is:

1. copy-paste the script into Script Editor (in Applications > Utilities)
2. select the cells with the dates
3. click the triangle 'run' button in Script Editor.

With those three quick steps this (left-aligned, not dates):

With the cells containing the "dates" selected, one click produces this (true date-time, right-aligned):

Then to sort so the most recent come first, click the down 'arrow' next to the column letter and choose Sort Descending.

Giving this (on my small subsample):

I have defined Row 1 as a Header Row so it stays at the top when the table is sorted on the date column.

If you don't like the yyyy-mm-dd format that results from the script just go to Format > Cell > Data Format and choose your preferred format from the Date dropdown (depending on your region settings your choices may vary from mine):

The script is below.

SG

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range)

set selRng to selection range

repeat with c in selRng's cells

tell c's value as string

set yyyy to text -4 thru -1

set mm to text 4 thru 5

set dd to text 1 thru 2

set theDate to yyyy & "-" & mm & "-" & dd

end tell

set c's value to theDate

end repeat

end tell

end tell

end tell

Hi Ross,

Nothing inept about your description. You want to arrange the items in order by date, with the most recent at the top of the list.

The GOAL is to sort the list in the desired order.

The purpose of the formula is to order the individual date's parts from largest (year) to smallest (day) to make it possible to achieve the GOAL.

Unfortunately, the examples chosen for your initial data sample (none with dates later than the 12th of the month) did not make it clear whether the bank statement expressed the date as dd/mm/yyyy or as mm/dd/yyyy, I assumed the second, and didn't pay close attention to the longer list, where it's clear from the first entry that the format is dd/mm/yyyy.

The intention of my formula was to arrange the three parts of the date, yyyy, mm, and dd in left to right order by size—year, month, day. Had my assumption of the initial order ( mm/dd/yyyy ) been correct, the formula offered would accomplish the task, and a descending sort of the column containing the reformatted dates ( yyyy/mm/dd ) would sort correctly.

For an initial date format of dd/mm/yyyy, though, a different formula is needed to reformat the date as yyyy/mm/dd.

B2: =right(A2,4)&"/"&mid(A2,4,2)&"/"&left(A2,2)

Original in column A, reformatted in column B. Sorted on column B

Regards,

Barry

PS: The only change in this post is the change in the formula used. All other instructions are the sam as in my earlier posts.

Regards,

Barry

HI Ross,

I don't think there's been any (recent) misunderstanding on this end of what you want.

You want to reverse the order of the list of items in your bank statement to bring the most recent item to the top, and place the oldest item on the bottom.

Here's a sample using just the first few rows of the two column list in your example of Sept 10, with dates running from June 30 (earliest) to July 10 (most recent). I've shortened the list and used only initials for the narrative column to reduce the typing.

I got from the initial order (on the left) to the desired order (on the right) through these steps:

1. I added a new column to the right side of the table by selecting a cell on the table, then dragging the Column control handle to the right.

2. I entered the formula supplied in my earlier post into row 2 of the new column, and filled it down to the end of the column to reversed the order of the elements of the dates to yyyy/mm/dd, an order that will sort correctly in ascending or descending date order.

Formula:

C2: =RIGHT(A2,4)&"/"&MID(A2,4,2)&"/"&LEFT(A2,2)

Fill down to the end of the new column.

3. I selected a single cell in the new column, then opened the contextual menu for that colum and chose Sort Descending.

Note (green arrows) that the earliest entry (Jun 30) in now at the bottom of the list, and the most recent entry (of the sample used) is at the top, that this is true in both the new column C and the original date column (A), and that the narrative entries are still associated with the correct dates.

At this point, the table is arranged as desired, and the new column can be deleted or hidden. If you think there will be a need to again sort the table into descending date order, keep the sort column, but hide it to prevent accidental changes. If the table is now in its final order, and there will be no need for a resort of any type in future, copy the formula to a safe place (so you have it for the next statement), then delete the sort column.

You wrote: "None of whats been offered seems to help and I apologise for wasting anyones time."

What have you tried?

What steps did you tke when you tried that? What results did you get at each step?

Where did the process go wrong?

Screen shots would help. To take a screen shot of a portion of your screen:

Place the mouse pointer at one corner of the rectangular space you want to include.

Press shift-command-4 (Hold down shift and command, tap 4, then release all three keys.)

The pointer will change to a cross hair. Click and hold the mouse button and drag the crosshair to draw a selection rectangle enclosing the part of the screen wanted in the shot. Release the mouse button to save the shot to your desktop.

To add the screen shot to your post:

Click the camera icon above the composing frame. A dialogue will open.

Click Choose Image. A choose dialogue window will open.

Select the file

(Hint: Click the label of the Date column to sort the list by date. If the top item on the list is now NOT "Screen shot…" click the Date label again to reverse the sort order.)

Click Choose. The choose window will close.

Click Insert image. The dialogue will close and the screen shot image will be inserted in your post.

Regards,

Barry

Hi Rossco,

Are the cells formatted as Text?

Select the cells and Format Panel > Cell > Data Format > Date & Time

Then sort descending.

Regards,

Ian.

"I’ve managed to open the formula bar, (just pressing the = symbol, who knew?) and I’ve entered your formula in

to a B column as instructed. Something happened. A date appeared. I’m guessing I have to enter that formula in all the way down the column then."

Hi Ross,

You do, but it's easier than you think. You already have the formula in B2.

There are two ways to fill that formula into the rest of the column.

1.

Click on cell B2. You'll notice a blue rectangle appears around the cell. The rectangle will have a number of small circles on it (see the images in my post above). In Numbers '09, there is one circle at the bottom right corner. In Numbers 3 (which you are using) there is more than one circle. Choose the one in the center of the bottom line in the rectangle.

Click and hold, then drag down. As you drag the rectangle will expand to contain more cells in the column, the formula will fill into each newly contained cell, and the result will be displayed. Continue dragging down column B until your selection includes the cell for the last date in the list.

OR

2

Click on cell B2 to select it, then press command-C to Copy.

Scroll to the bottom of the column (or the last row containing a 'date') and shift-click on the cell in that row of column B to select all cells between it and B2.

Press command-V to Paste.

The formula will be pasted into all selected cells, and the results displayed.

Although the table may now be sorted descending on the values in column B, those values are still dependent on the values in column A. The following two steps replace the formulas with the displayed results. Once that is done, these dates are fixed, and column A may be deleted if desired.

With all of the column B cells still selected, press command-C to Copy.

Now go to the Edit menu and choose "Paste formula results." (wording may be slightly different)

Regards,

Barry

PS:

"Command-C" means 'press and hold the command key, tap the C key, release (both) keys.'

"shift-click" means 'press and hold the shift key, (left) click the mouse, release the mouse button and the key.

B

Hi Ross,

One last attempt that may be a little simpler.

It works only if your goal statement—to reverse the order of the list of dates (and, of course, the rest of the data)—is precisely correct.

1. Add a column to the table. call it "Sort"

2. Enter the numbers in order from 1 to however many rows there are in the table into the new column. (There are several ways to do this, listed below.)

3. Sort the table descending on this column.

Done.

Ways to do step 2:

A. Type 1, press return; type 2, press return; type 3, press return; etc.
Issue: Tedious. Takes too long

B. Enter 1 in the first row to be sorted and 2 in the second.
Select both cells.

Drag the Fill handle down the column to fill the series of numbers into the cells.

Advantage: Less typing. Faster than method A.

Issue: Still slow, especially with large tables.

C. Enter =ROW() into the first row to be sorted.
Select that cell and Copy.

Click the column tab/bar above the cell with the formula to select the whole column. Command-click each cell above the first row to be sorted to deselect these cells. Paste.

With the whole column still selected, Copy, then Paste Results to replace the formula with the fixed values which have been calculated by it.

Advantage: Very little typing or dragging. Fastest method with a large table.

Issue: Must remember to Paste Results for second paste, to prevent formulas from recalculating after the sort.

Regards,

Barry

PS: Regarding posting screen shots of your bank statement:

There's no need to post the actual data beyond what's necessary to demonstrate where issues are occurring. A sample (10 rows or so) containing only the Dates column and an index column, if one is used, should be enough to demonstrate where the errors are.

The "Narrative" column in my example above served only to demonstrate that the data in column B stayed associated with the dates in column A. That was 'real' data only because it matched dat on your sample table. It could have easily been replaced with a letter indicating the column and a number showing the row that originally contained that 'data.'

B

Hi Ross,

Just adding to Barry's comment about Date formats. Different regions have different preferred orders for Day, Month, Year.

I like to show the month name and full year (YYYY), as that is more meaningful to those in other regions.

Have a look at the choices under Format Panel > Cell > Data Format > Date & Time. Your choices may not exactly match my choices.

Regards,

Ian.

Replied on the 11th day of September in the year 2015 😉.

Hi Ross,

Add another column (A)

Type 1 in A2 and 2 in A3

Select both cells.

Drag the yellow Fill Handle down to the bottom of Column A

Those are numbers, not formulas.

Sort the table by Column A Descending.

Done!

Regards,

Ian.

Hi Ross,

As Ian implies, Numbers is not recognizing those values as Dates. Formatting the cells as Date and Time will likely solve the issue. If not, then try these manipulations which rearrange the 'date' text strings into an order that will sort correctly whether Numbers sees it as a Date and Time or as text.

Add a column to the right of the 'dates', then enter this formula beside the first 'date' and fill down to the last. Formula assumes the dates are in column A and the formula is in column B:

=RIGHT(A,4)&"/"&LEFT(A,5)

Results: Sorted descending on column B:

Regards,

Barry

Hi Ross,

Thanks for the green tick and your feedback. It seems that the forum software posted my reply before Barry's reply.

Hi Barry,

Great minds think alike! I like your solution of

=ROW()

and

Must remember to Paste Results for second paste, to prevent formulas from recalculating after the sort.

Nice one!

Regards,

Ian.