Trying to force a date format 'd-mmm-yy', not working.

Hello,

I have worksheets named by dates in the format 'd-mmm-yy', for example 5-Jan-14. In that worksheet there are rows of data with dates that are the same as the sheet name. It may happen that a row of data will have a date <> 5-Jan-14 and would need to be moved to a new worksheet with the correct date.

I have written an algorithm for this but there is a weird problem with dates in the format 'd-mmm-yy' where the day consists of a single digit. It seems the date in the worksheet for the case above is being recognized as '05-Jan-14'.

To try and debug, I'm copying the Sheetname to a couple of cells via:

Range("C22") = Worksheets("5-Jan-14").name
Range("C23") = Format(Worksheets("5-Jan-14").name, "d-mmm-yy")

With the above:
cell 'C22' = '05-Jan-14'
cell 'C23' = '05-Jan-14'

A 'right click', 'format cells...' on either of the results above shows a custom number format 'dd-mmm-yy', whereas prior to the results assigned the cell formats were 'General'.

In addition, the dates found in the rows of data have a format of 'Date', '14-Mar-01', as shown in the format interface corresponding to 'd-mmm-yy'

When a date from these rows is assigned to a variable 'myDate' defined as 'String' and I then do the following:

Dim myDate As String
myDate = Cells(3,3)
Range("C25") = myDate
Range("C26") = Format(myDate, "d-mmm-yy")

With the above:
cell 'C25' = '01/05/2014'
cell 'C26' = '05-Jan-14'

In both cases the format is not being forced as desired!

What's going on? Any help is much appreciated.

Thanks,

TV

xlbiznes,

Thanks for the reply but it did not change anything.

TV