6-18-2021

This is an extremely elementary question but that's the level at which I work: Please see the small attached spreadsheet "Abbreviated Excel spreadsheet.xlsx"

This is just the first few rows of a longer spreadsheet downloaded from my bank of an Excel file showing the transactions in a credit card account with that bank which occurred a short time ago. It was originally downloaded as as .csv file which I then saved as a .xlsx file and in that format manipulated it a little further Excel Office 365.

Please see the first column headed "Date" showing the date of these few transactions, all in May, 2021. Every single date appears in the format dd/mm/yyyy-- they appear as 05/18/2021, 05/22/2021 and 05/25/2021. There are dozens more such identically formatted dates in the larger complete spreadsheet which I downloaded from the bank.

NO MATTER WHAT I DO, I CANNOT GET THIS FORMAT TO CHANGE. I WOULD PREFER NOT TO SHOW THE INITIAL ZERO. I follow the usual instructions: after selecting this column-- I go to HOME--> Number-->click on the small box at the lower righthand corner of that window-->CUSTOM and see the list of different available formats. NO MATTER WHICH ONE I CHOOSE, NO CHANGE OCCURS IN THE APPEARANCE OF THE CELLS IN THE DATE COLUMN(???). ALTERNATIVELY, IF I CHOOSE HOME-->


NUMBER--> DATE-->AND THEN SELECT FROM THE SHORTER LIST OF DIFFERENT FORMATS, AGAIN NO CHANGE OCCURS WITH ANY OF THE OPTIONS CHOSEN.

At first I thought the problem might be that I had inadvertently managed to "lock" the cells and prevented formatting from being changed. As best I can tell, nothing is locked. What don't I understand about this process that absolutely no change in these cells occurs according to expectation from instructions given in multiple online help articles and youtube video clips? But in addition, what is the difference between these two different ways of trying to format dates? Assuming both happened to work, which one is preferable?

Whoever responds, please understand this is not merely a cosmetic issue. If I leave the date format alone and try to paste the Excel spreadsheet as it stands into a QuickBooks page designed to allow me to load the transactions from the Excel file into my QuickBooks account ledger(s), the paste fails and messages appear saying that the date format is incorrect.

Lastly, can anyone explain why the first date listed in that column is not aligned with the dates of the two transactions immediately below it? The two lower transactions stay together when I ask for right-justification, left-justification or centering of the column. But the first of the three transactions always moves independently of the other two, even after I line it up by hand with the two lower transactions: It will remain centered with them, but it will not be aligned in any other justification choice.

This oddity occurs only in the date column and only in the first row of data in any download of an Excel file from my bank. And not elesewhere. Why?

What gives?

Thanks.

geobar.

Attached Files

Abbreviated Excel spreadsheet.xlsx (11.4 KB, 7 views)

Column A is text with non printable characters and spaces, then it is also in US format (maybe a setting in Excel or your bank).

I would add another column with the below formula

=MID(TRIM(CLEAN(A2)),4,2)&"/"&MID(TRIM(CLEAN(A2)),1,2)&"/"&MID(TRIM(CLEAN(A2)),7,4)

then in the next column add zero to make it a number

Probably can be done in Power Query but i have hardly ever used PQ

Attached Files

Abbreviated Excel spreadsheet_ar12.xlsx (17.5 KB, 6 views)

6-16-2021


Dear Purfleet:


1. Jeez!


2. Thank you very much.


3. This is way over my head; I'm not sure which disturbs me more-- the complexity of the formula or the speed of your response with it.


4. Would you please be much more specific in offering the instruction: "then in the next column add zero to make it a number"--when you say "in the next column" are you speaking about a column that this formula will create which does not exist before the formula acts upon the given Date column? This is a little confusing-- you first ask me to create a blank column (I assume to the right of the existing Date column) and then speak of "in the next column" which as the spreadsheet stands now contains information describing the payee for the transactions-- I'm sure you don't mean add a zero to any of those cells. You must mean add a zero to data that will appear in the initially blank column just created to the right of the Date column, don't you? And where exactly will that zero be "added" in each cell? "Added" how? To the very end of whatever digits are displayed in that column?


You see the difficulty I have visualizing this.


5. Are you absolutely sure there is no simpler way to convert the text information in the Date column to numerical information on which Date formatting can act?


Many thanks. I'm most impressed.


geobar

Did you see the example in the attached worksheet?

I put the formula in column E, then in column F i have added zero to make the date. you can then copy this column and paste special > values and formats back in column A and delete the formulas

re point 5 - this is how i would do it, but if i have learnt anything about Excel its that there will be 5 or 6 ways to to it

Another possibility
To clean "dirty" data, you can use in col E
and to make it real dates in col F and pull down


you can skip the second step this way :

- copy results of col E
- Paste values somewhere say col F
-select the range in col F
-Select Data Text to columns - Next - Next
- In the last window set the date format to MDY and click Finish - If results are right aligned you're good
( Can't say this is much easier... :-))))

Isnt that the same as mine but in 2 cells? Just wondering if it was easier

The first formula I proposed can be used in all cases where strings need to be cleaned, while yours is specific to cleaning and converting to dates at the same time.
It also introduces the technique for converting dates ( when applicable) using the Text to Columns feature

IMO, using helper columns ( created by the first formula) is much more easy when it comes to finding errors and is easier to understand

In your ( excellent) solution errors would be harder to find
A nice article on cleaning "dirty" data

@geobar
Please don't use uppercase in your posts unnecessarily. They are the equivalent of shouting, and as we are all friends here...

Fair enough

I must admit that i generally use a few stages before joining it all together as it is usually a request to have a condensed version - although i agree much hard to decode

You're right about that, and in most cases the OP does nothing but copy/paste what is proposed without learning or trying to understand how the solution works