Santander statements to excel issues

[frit]

Anyone else had issues trying to sort by date once it's in Excel? Ive downloaded the file no problem and it opens in excel but it seems the dates are in different formats depending on what the transaction is. Took me ages to work out that some dates are in a 5 figure format and some aren't. I've managed to convert it to d/m/yyyy but the rest are dd/mm/yyyy and I assume therefore the reason it doesn't sort by date order, instead it sorts by numerical order. I've tried using the format number and clicking date or text or number, makes no difference . I am using excel online rather that the proper software, no idea if that makes any difference. Any help or experience much appreciated.

[mmm-five]

Don't convert any of them manually.

You should be able to just highlight the range and use the "Text to Columns' function to convert what seems to be numbers stored as text into proper number/date format.

If it's the same as the offline version you can just click [Next] in Step 1; [Next] in Step 2; and [Date] in Step 3.

ETA: just logged in to the web version and it's in the same place under the [DATA] tab.

Edited by mmm-five on Wednesday 4th January 19:11

[frit]

Thanks, I had tried to use that after a lot of Googling but it didn't come up with the same menu as it suggested on Google and sounds like you are referring to. I am well out of my depth here but that button for me, gives you the option to choose tab/semicolon/comma/space and then apply as opposed to the step by step wizard that Google and I belive you are referring to.

[mmm-five]

frit said:

Thanks, I had tried to use that after a lot of Googling but it didn't come up with the same menu as it suggested on Google and sounds like you are referring to. I am well out of my depth here but that button for me, gives you the option to choose tab/semicolon/comma/space and then apply as opposed to the step by step wizard that Google and I belive you are referring to.

Yes, looks like the online/free version is purely to split a cell entry by a delimiter, without the further options to convert.

This is what I get on the Mac, and Windows version is identical/similar (can't take screenshots from that as it's locked down by the company)...

[frit]

Thanks for your help, looks like I need the full software. Back to the drawing board!

[mmm-five]

frit said:

Thanks for your help, looks like I need the full software. Back to the drawing board!

Well, I'd normally offer to help, but the content is personal.

Although if you can extract the dates as a single column to another file I'll happily convert it for you, and then you can copy & paste it into your original file.

[frit]

I've managed a very heath Robinson solution of putting a number from 1 to n next to the transactions on the unmodified version, as it is in date order when it's downloaded, then I can sort by the number column to sort by date and just read the date thanks again

[MOBB]

Maybe try ASAP utilities which has a utility for this kind of issue

[MOBB]

Maybe try ASAP utilities which has a utility for this kind of issue

[Jenny Tailor]

Download as a CSV and import into Excel??

[Paul Drawmer]

When I download my Santander transactions they're in date order.

[rdjohn]

Mine also, using Office 365

[frit]

It was more trying to resort to date order after sorting by amount or description etc.

[anonymous-user]

Send me your Santander details and i'll give it a go

[Paul Drawmer]

frit said:

It was more trying to resort to date order after sorting by amount or description etc.

Try inserting a new sequentially numbered column and sort on that then. Or can't you select the date column and do a date format?

[paulrockliffe]

Download the file and put it in a folder somewhere, store it in .csv format if possible, I don't know what options Santander gives you.

Then in Excel open Power Query and create a new Query from Folder. Navigate to your folder and load all the files there. This means to keep your data updated you simply drop new files in there and they'll be combined into one table.

In Power Query you have all the tools to munge (technical term) your data so that this issue goes away, some sort of conditional bish bash then bosh it into a Date format when it's fixed.

When you have the table loaded into Excel, you can drop it into whatever Pivot Tables you need to answer whatever your question is. Much simpler and will keep itself up to date as you go without any more messing about.