It sounds like you have a mix of formats coming from the external
databases, and you may need to perform different conversions based on
each entries current format.

As a starting point, recall that in Excel, a date is an integer number
and the cell that holds that number is formatted to show a date. For
instance, the integer equivalent of 8/29/2006 is 38958. (Try it: enter
today's date in cell A1, then in C1 enter the formula =A1 and format C1
as a number.)

You can check to see which cells will require conversion with the
formula
=ISNUMBER(A1) which gives a True / False response: the False entries
will need conversion.

You're on the right track with the DATEVALUE conversion. The
=TEXT(G2,"dd/mm/yyyy") conversion yields a text answer, which will not
correctly compare to a numeric date entry. You may want to check those
cells for a leading apostrophe in the cell, or leading or trailing
blank spaces, or other text type entry that would prevent it from
evaluating as a number.