Comparing dates does not work

[Excel_Arate]

Hello,

In a code, I compare the dates of a master File with those of a target file to determine where the last entry has been made. The code reliably conducts the comparison of Cell B37 of the target file with cell A12 of the master cell. For some reason, however, the code does not recognize when the dates are the same.

The relevant part of the VBA:
In the target file, "Date_new" gives me the last entry (January 6, 2023), which in this case is in cell B37:

DATE_new = ActiveCell.Value

Switching over to the master file...

Windows("Current_Market_Data_Europe_extract_comparing dates_Master.xlsm").Activate

That date is to be found in a table:
If Cells(i, 1).Value = DATE_new And Cells(i + 1, 1).Value <> "" Then

N.B. A "For...Next" loop relaibly leads to a value where cells(12,1).Value is addressed, i.e. the cell in the master file containing the entry "Jan. 6, 2023".
Problem: The comparison doesn't work, despite the dates being the same (January 6, 2023), including the formatting. Even when I make a direct comparion in Excel (see Master file cell G25), the statement that they are equal gives me a "false". Why are the two dates with the same formatting regarded as not being the same?

Excel_Arate

[JohnTopley]

Dates in Column A are TEXT which you are comparing (G25) against an Excel (numeric) date

06.01.2023 vs 44932

Hence FALSE

This will return TRUE

Please Login or Register  to view this content.

[davesexcel]

The formats are not the same, one is text and the other is a date value.

You have a formula where you are spliting two dates in a cell.

Please Login or Register  to view this content.

Instead of trying to manipulate the dates in other sheets, you can remove the double dates in DAX_MW sheet.

Please Login or Register  to view this content.

[Excel_Arate]

Hi John,

Thank you very much, yes, that seems to be it (and sorry for the late reply)! I actually thought it might have something to do with the formatting and checked the seetings in Excel. Attached in the word file you will see two screenshots for the settings of the cell (please apologize the German, but the words should be self-explanatory). Now how come in both cases, I'm shown that it's a date (whne in one case it's a text)? That still confuses me...
Excel_Arate

[Excel_Arate]

Thanks Davesexcel, Yes, good now I know where the formatting as a text comes from, thank you for pointing that out. I did sense that the problem might have something to do with the formatting. However when I checked the cell settings, both indicate that its' da date (please see the two screenshots in the word file and please apologize the German, but the words should be self-explanatory). Do you know the reason for this?

[davesexcel]

It doesn't matter if the cell is formatted as date, the cell is filled with text.