Change Date from "DD.MM.YYYY" to "MM.YYYY"

Dear Members,

I have the following problem in EXCEL. I have two lists of data, both as a time-series, meaning the data within the list is linked to a date. I now want to match some Data of the two lists by using XLOOKUP and the Date. The problem is, that it is monthly data but the date for the same data is different (for January 1965 I have 31.01.1965 and 30.01.1965). Therefore, XLOOKUP does not work. I already tried quite a lot of different approaches but none of them converted the two Dates into identical monthy dates so I could apply XLOOKUP.
Does anyone know how to convert these different Dates into the same "Value"/monthly dates? The final format of the date is not important, whether it is "January 1965", "01-1965", "1965-01" or something else.

Thank you all very much in advance.

Sincerely
Dennis

A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.


The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.


Here are the instructions, found at the top of the page again

HOW TO ATTACH YOUR SAMPLE WORKBOOK:, Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated Expected results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

dates are just numbers starting jan-1900 and 1 added for each day
so
the number for 31/1/1965 is 23773
change to general and you will see the numbers
30/1/1965 is 1 less 23772

So changing the format , as you say, just changes the way it looks

so using xlookup - what are your expected results and why

cannot see what you are doing on the sample

I now want to match some Data of the two lists by using XLOOKUP and the Date.

link to do what

You can make XLOOKUP work WITHOUT changing your date formats.

=XLOOKUP(TEXT(B2,"mm/yyyy"),TEXT(Data2!$B$2:$B$13,"mm/yyyy"),Data2!$A$2:$A$13)

You have attached the same workbook again - why? Where are your expected results? If they are noit there, mock them up, please. SHOW me what you want.

Please add the Excel product (365) to your forum profile.

One more guess. On Data2 in D2 copied down:

=XLOOKUP(A2&TEXT(B2,"mm/yyyy"),Data1!$A$2:$A$15&TEXT(Data1!$B$2:$B$15,"mm/yyyy"),Data1!$C$2:$C$15)

I asked for you to mock up expected results, not use a non-functioning formula.

Once again, this in D2 copied down should do what you want:

=XLOOKUP(A2&TEXT(B2,"mm/yyyy"),Data1!$A$2:$A$15&TEXT(Data1!$B$2:$B$15,"mm/yyyy"),Data1!$C$2:$C$15)

See the attachment to my previous post.

Here it is in your second workbook.

But to make the unique identifiers identical in both sheets, the date has to have the same intrinsic value. This is why I am trying to reduce the date to month and year to make them similar in both sheets.

You do not need a unique identifier with my formula. The formula deals with the dates by using just the month and year. I think that you are not understanding how my formula works.

Are we there yet, Dennis?

Please add 365 to your forum profile, as requested earlier.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Marked as solved, but no reply. A 'thank you' might have been nice.

You're welcome. But you still haven't updated your profile - please do this as soon as you can.

You should never be in too much of a hurry to acknowledge help.

Thanks for updating your profile.