Excel/VBA - incorrect interpretation of a date

  Heefie 12:20 01 Jan 2005
Locked

In an Excel spreadsheet, I can enter DD/MM manually and I get Month DD, YYYY displayed in the Cell.

However, when I put this exact same DD/MM date into the Cell via a VBA macro, I still get the output displaying as Month DD, YYYY , but it has been interpreted, incorrectly, as MM/DD, not as DD/MM !!!

For example, I enter 01/02 into the Cell & press Enter ... I get the result February 1, 2005 displayed in the Cell.

I now run my VBA macro :

MyDSRec is defined as STRING and is a line read from a TXT document.

It's value is "NELSON3 17/11 25/11 25/11 01/02"

The command "ActiveCell = Right$(MyDSRec, 5)" successfully places "01/02" into the Active Cell, but it dispays in the Cell as January 2, 2005 ...

How do I correct the Formatting so that this displays correctly ?!?!

  VoG II 20:51 05 Jan 2005

I've put your "NELSON3 17/11 25/11 25/11 01/02" into B2 for reference.

The folowing then does what you want:

ActiveCell.Value = Format(Right(Range("B2"), 5), "MMMM d, yyyy")

  VoG II 20:59 05 Jan 2005

ActiveCell.Value = Format(Right(Range("B2"), 5), "MMMM d ,yyyy")

  Heefie 06:45 08 Jan 2005

Somebody else had given me a way around it, but as usual you come up with a more efficient solution ... thanks ...

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Turn a photo into 16-bit pixel art

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?