Macro changes date formats to mix of US and European

[macdca]

I have a macro that used to work OK, until I changed excel from 2003, to another 2003 (rebuild). This shouldnt have made a difference but my dates in the worksheet which the macro pasted to are a mis of us and UK dates (where there is dubiety in the date, when the month could be less than 13, its changing the format to US, whoch is wrong). I cannot sort on these dates now wither. Does anyone know how to hard code a date format into my macro so that the dates will stay as Uk format whatever version of excel I use or whatever PC??

Thanks

 

[lmvk99]

If you want to put it into your macro to fix all of the date formats to the one you want you can just run:

For Each cell In Sheets("Your sheet").Range("Your range")
cell.NumberFormat = "dd-mm-yy"
Next

You can change the bolded date format in the above code to whatever you want and you can use the custom number formats list as a guide.

Good Luck!

 

[macdca]

Thanks for that. What do I enter in the range if I am interested in everything in columns F, G and H?

 

[lmvk99]

Try use this instead:

Columns("F:H").NumberFormat = "dd-mm-yy"

 

[macdca]

<TABLE style="WIDTH: 64pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=85 border=0 x:str><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 64pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 18pt; BACKGROUND-COLOR: white" width=85 height=24>30/11/1952</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24 x:num="16529">02-04-45</TD></TR></TBODY></TABLE>

This gives me a mis of data formats on my worksheet like those above, the latter being in the US format which I dont want, and both ,eaning I cannot sort on this field?

 

[lmvk99]

Hmmm, I can't recreate your problem, but it sounds to me like maybe your data is being brought in as text? Try using excel's text-to-columns feature. I believe in 2003 it is under the data menu. It will step you through a wizard.

 

[macdca]

I tried that and it still doesnt work, I still end up with a mix of date formats.

Thanks anyway