Sep 23 2020 12:27 PM
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.
Sep 23 2020 12:31 PM
As variant you have texts, not dates, but better to check sample file with few records.
Sep 23 2020 12:32 PM
The values are probably text instead of dates. Could you attach a small sample workbook without sensitive information?
Sep 23 2020 12:44 PM
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. @Hans Vogelaar
Sep 23 2020 12:53 PM
The values are text indeed. You can convert each of the columns to 'real' dates as follows:
You should now be able to compare the dates.
Sep 23 2020 01:01 PM
I tried that, but it's the dates are not matching.. @Hans Vogelaar
Sep 23 2020 01:05 PM
I ma sending a sample file with the dates. Please have a look. @Sergei Baklan
Sep 23 2020 01:38 PM
That's exactly as @Hans Vogelaar 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:
Sep 24 2020 10:52 AM
Dear Sir,
The date format in the old data sheet can be changed, the date format in the new data sheet is creating problems. If you could perform concatenation in each of the sheets using the formula-
=CONCATENATE(A2,B2,C2,TEXT(D2,"YYYY-MM-DD"),TEXT(E2,"YYYY-MM-DD"),F2,TEXT(G2,"YYYY-MM-DD"),TEXT(H2,"YYYY-MM-DD"),I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2). I am sending the worksheet where I have performed a calculation. Please have a look,I basically want to match the date formats ,@Sergei Baklan
Sep 24 2020 01:22 PM
I'm sorry, but I didn't find where is the formula from the post within workbooks. It has lot of sheets and different columns, not clear to what and which formula you'd like to apply. To illustrate just couple of records in one sheet will be enough.
If you apply formula like TEXT(D2,"YYYY-MM-DD") for the D2 which has text value, it will do nothing. You may simply use D2, result will be the same. If you'd like to present the text in another format, first you shall convert it to date (aka number) and after that use TEXT() with new format. You may convert entire column first, or within formula. If in your locale default date format is the same as in text, you may use TEXT(VALUE(D2),"YYYY-MM-DD"). If not, first parse the text and convert to date then, like TEXT(DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,4,2)),"YYYY-MM-DD")
Sep 26 2020 01:00 PM
sheet tab-new data sheet.
it displays columns that have dates from the new sheet.I have used the formula "=CONCATENATE(TEXT(A2,"YYYY-MM-DD"),TEXT(B2,"YYYY-MM-DD"),TEXT(C2,"YYYY-MM-DD"))".
The date format does not change.
the column labeled new concat- displays concatenated data for all the 3 cells.
Sheet tab-old data sheet
it displays the columns that have date formats from the old data sheet.
I have used the same formula "=CONCATENATE(TEXT(A2,"YYYY-MM-DD"),TEXT(B2,"YYYY-MM-DD"),TEXT(C2,"YYYY-MM-DD"))".
the column labeled old concat- displays concatenated data for all the 3 cells.
Sheet tab-result:the old concatenated data and new concatenated data are placed side by side.
then the condition is applied: =IF(A2=B2,1,0).
All the results are displaying 0, even though the dates are same, but formats are different.
My aim is to match the results, i.e to bring the output as 1.
Please help me to do so.
Sep 26 2020 01:33 PM
Look, if you use formula like
=CONCATENATE(TEXT("a","YYYY-MM-DD"),TEXT("b","YYYY-MM-DD"),TEXT("c","YYYY-MM-DD"))
it returns exactly the same as
=CONCATENATE("a","b""c")
i.e. "abc". TEXT() function changes nothing in value if it is text, doesn't matter which format do you use. In the file practically all data are texts in form of dates. Dates in Excel are sequential integers where 1 is equal to Jan 01, 1900. You may apply any format to the text "07/28/2020", Excel will keep it as text "07/28/2020". But if you enter in any cell number 44040 and apply to it "YYYY-MM-DD" format, it will be shown as date 2020-07-28. Behind it's still will be number, not text.
Thus first you shall convert texts to dates as it was explained earlier. With that you may concat without any formatting if only you need to have strings to compare, like
Oct 01 2020 12:37 PM
Dear Sir,
I am trying to apply text to columns on the New Data Tab of the excel sheet that i have sent- but it is not being converted to dates.
I have used the exact method-: select column of the excel sheet>Data tab on the ribbon>Text to columns>Next>Next>choose YMD>Finish,
I even selected the columns one at a time. The data on the New Data Excel Tab hasn't shown any change. Can you please confirm if something is wrong with the dates on the New Data Tab
Oct 01 2020 02:33 PM
It works, please check attached. Hard to say what was wrong in your case.
Oct 01 2020 03:12 PM
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)
Oct 02 2020 06:51 AM
Dear Sir,
Could you please send a screen shot of this settings window on your computer. This is the screen shot of the settings on my computer. I don't know whether there is any issue in the settings because of which the date formats are not changing.
Oct 02 2020 07:16 AM
Oct 02 2020 07:47 AM
@lotusana003 , I have exactly the same settings. Perhaps you have texts instead of dates.
Oct 02 2020 07:50 AM
In your case it shall be not YMD, but MDY. Here is format of origin, to the one you'd like to have.
Oct 02 2020 08:21 AM