Getting a string that represents the date as it's displayed in the cell

[Bezzie]

Hi

I have a bunch of sheets with lots of dates on each sheet. My problem is some dates were entered as 2003/07/06 (yyyy/mm/dd) and others were entered as 06/07/2003 (dd/mm/yyyy) in the cells (the dates came from different sources and some of them were typed in as well). In all the cases the date is displayed as 06/07/2003 (dd/mm/yyyy). VBA is seeing both formats as dates but when working with the cells one is interpreted as 06/07/2003 and the other one is interpreted as 07/06/2003 because of the different formats in which it was entered.

I want to make a subroutine in VBA that can go through all the sheets and make the dates the same but alas...

Is there a way I can read the date from the cell into a text string as it is displayed in the cell?. If I can do this I can then force the date into a format that is consistent over all the cells.

There is literally thousands of dates so changing them by hand is impossible.

Or any other ideas on how I can get the dates in one format will be appreciated?

[BigBas]

Try this code:

Please Login or Register  to view this content.

[Bezzie]

I don't understand. Using the .NumberFormat returns "dd/mm/yyyy" for both types of dates, the ones entered as 2003/06/07 and 07/06/2003. How do I use it to turn a date entered as 07/06/2003 into 2003/06/07 so that it will conform with the other dates that was entered as 2003/06/07 in the first place?

[jaslake]

Hi Bezzie

Without a Sample File it's a bit difficult to know what you're dealing with...perhaps this will give you a start.

Please Login or Register  to view this content.

[Jakobshavn]

If you have data in A1 (Date or other data) then:

Please Login or Register  to view this content.

gets the value as displayed.

[Bezzie]

Got it solved. VarType() function of VBA made a distinction between the two date formats so was able to 'see' which ones I needed to change. Used DateValue() with Mid() functions to convert the wrong ones. Thanks for the inputs.