Date format and vba weekday problem

[Flowerski]

Dear friends. I gave up so there is this thread. I am starting with file in .txt created by the outside device. The date is always in format d-mmm-yy. Can not change it (why?). Then I am copying /pasting the sheet on the sheet in excel. I am still not able to change the date format. THe vba code is not calculating following thing = worksheetfunction.weekday(cell1). If cell1 is in format m/d/yy then worksheetfunction.weekday works well if the cell1 is in format d-mmm-yy worksheetfunction.weekday(cell1) does not work. THe bad thing is that I am not able to change the date format after copying from .txt and pasting in excel sheet. I am not able to change date format before copying. Any suggestions??

[MrShorty]

Then I am copying /pasting the sheet on the sheet in excel. I am still not able to change the date format.

It is difficult to be certain without an example, but my guess is that Excel/VBA is not recognizing the date as a real date, so it is importing it as a text string.

One solution might be to look at the importation step. Perhaps somewhere in the importation step you can change something so that Excel/VBA will recognize it as a date.

The other solution would be to use Excel's text manipulation functions to extract the date, month, and year, then recombine into something Excel/VBA can recognize as a date using either the DATEVALUE() or DATE() functions.

The exact implementation of either of those solutions depends on exactly what your file/code is doing.

[Flowerski]

Thanks a lot. Will try your suggestions.