Dates Not Copying Correctly

[Justinian]

I am copying dates from an external database to Excel in column A. When I do, dates appear in the format of "mm/dd/yy" (so today shows as 01/28/24). If B2 = today's date and I run the IF statement that says =B2=A2, the return is FALSE even though both A2 and B2 have 01/28/24 in the cells. When I manually type 01/28/24 in A2, all of a sudden B2 returns TRUE.

Why is this happening and how can I fix without re-typing all the dates in column A?

 

[PeteWright]

Is your Column A formatted as (Short or Long) Date? If it's Text, that could be the issue (even if it looks like a date).
The default format is "General"

 

[SanjayGMusafir]

Convert Column A to numbers and change back to dates. Then check the correctness of Dates. Some dates do get distorted in the process... If any you have to change them manually. Not all.

 

[Fluff]

Sounds like the dates are text, to convert them to dates, select column A, Text to columns (on the data tab), delimited, next, clear all checkboxes, next, select MDY from the date option, Finish.

 

[Justinian]

Is your Column A formatted as (Short or Long) Date? If it's Text, that could be the issue (even if it looks like a date).
The default format is "General"

I tried both (long and short date) and neither worked.

 

[Justinian]

Convert Column A to numbers and change back to dates. Then check the correctness of Dates. Some dates do get distorted in the process... If any you have to change them manually. Not all.

Tried that and it did not work. This data is imported so I cannot be changing all the dates manually.

 

[Justinian]

Sounds like the dates are text, to convert them to dates, select column A, Text to columns (on the data tab), delimited, next, clear all checkboxes, next, select MDY from the date option, Finish.

I cannot do that every day when I import the data. There has to be a better way.

 

[MARK858]

There has to be a better way.

Record yourself doing the steps, save the macro in your personal workbook and add it to your QAT (although doing it manually takes about 5 seconds and so I don't really see the issue)

 

[Justinian]

Not sure what a QAT is and manually retyping hundreds of imported dates takes more than 5 seconds. The point is for the end user to import data and access the dashboard without having to modify the source data.

 

[MARK858]

and manually retyping hundreds of imported dates takes more than 5 seconds

Fluff's method does not require any retyping of dates (1 column selection and 5 mouse clicks [or 4 mouse clicks and a dropdown change])

Not sure what a QAT is

The Quick Access Toolbar (QAT) is the row with the red arrow pointing to it (if you can't see it then enable it in Options)