Forum Discussion
Microsoft excel date format-not changing at all
I download an excel file(.xls) format from a particular portal that has been designed by the IT developers in my company. the downloaded excel sheet displays a date format which cannot be changed at any cost. i have tried custom format, text to columns,short date.. every possible method.. even used formulas like
=TEXT(H2,"YYYY-MM-DD"), but to no avail. There are basically 2 excel data sheets from 2 different portals that need to be matched, and for matching the data sheets, the date formats of the 2 data sheets should match as well along with the additional criterias. And this is a problem which has surfaced,only on my Windows excel 2010 version. Please help.
29 Replies
What the previous ones, who very well known of the subject - Excel, told you is absolutely correct.
Just add the columns to the format where you wanted, as I understood.
If this is not what you are asking for, please ignore my message.
Have a nice day.
Nikolino
I know that I know nothing (Socrates)The values are probably text instead of dates. Could you attach a small sample workbook without sensitive information?
I am sending an excel sheet with only the date columns for the new and old data sheet. Basically I have to concatenate each of the cells and then match the final concatenated data using the "IF" statement. But since the date formats for both the sheets differ, so the end result is a mismatch. Please have look.. I was wondering if had something to do with the settings of my Windows Excel. HansVogelaar
The values are text indeed. You can convert each of the columns to 'real' dates as follows:
- Select a column with dates, e.g. A2:A158.
- On the Data tab of the ribbon, click 'Text to Columns'.
- Click 'Next >' then 'Next >' again.
- In Step 3, select YMD from the Date dropdown.
- Click Finish.
You should now be able to compare the dates.
As variant you have texts, not dates, but better to check sample file with few records.
I ma sending a sample file with the dates. Please have a look. SergeiBaklan
That's exactly as HansVogelaar said. It works column by column (you can't apply wizard at once for more than one column). And you have different source format in new and old sheets. In attached file I converted for Column A only
old sheet:
new sheet:
