Checking and Changing Date Formats

[Tellm]

I automatically import data from a variety of sources – some of this data comes in as mm/dd/yy where other comes in as dd/mm/yy and I need it all as dd/mm/yy. Does anyone know how you can check the middle part of the date format to see if it is the current month and then if it identifies it as being incorrect then change its format accordingly? The importation is always done mid and end month so having a previous months date doesn't occur.

[rscsmith]

Hi Tellm

Here are a Sub and a Function that might do what you want. You will need to pick one or the other.

Please Login or Register  to view this content.

Best regards. Rob.

[rscsmith]

PS - you can do this with a formula, rather than a macro. The formula would be:
=IF(MONTH(A1)=MONTH(NOW()),A1,DATE(YEAR(A1),DAY(A1),MONTH(A1)))

Best regards, Rob.