format dd/mm/yyyy date as text in VBA without US conversion

wilcowoods

Active Member
Joined
May 29, 2008
Messages
275
Hi guys - tricky one I think.

I'm using 2007 / 2010 Excel to convert multiple columns from date to text format in VBA whilst retaining the dd/mm/yyyy format.

If I use Text To Columns manually and just select text at the end (without parsing any data), it works fine. If I code this, it then converts the dates to text but in the US date order (so mm/dd/yyyy).

Any ideas about how to navigate around this?

Any advice gratefully accepted.

Regards
Will
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I'm not 100% sure about 2007, but in the 2003 version's Text to Columns, when you choose to convert a column into a date, it has another drop down box with what format you want the date stored as.

Otherwise, you can highlight the column, format cells, and give it the custom format dd/mm/yyyy
 
Upvote 0
Nothing so simple I'm afraid. I want the column formatted as text while avoiding the date code that underpins all excel date formats. By using text to columns system you convert the column to text format and retain the dates BUT this only works when when you do it manually, not with VBA.

To complicate matters further Text to columns will remember the way it was last used and you can't switch this off in VBA so you are liable to unforeseen results. I have a way round this actually so if I can fix the switching that will be fine.

I've tried all the obvious pre and post-formatting options but nothing works like it does when you do it manually!

Help!
 
Upvote 0
VBA is US-centric when it comes to dates. I don't know a solution using Text To Columns. You will need a loop like this:

Code:
Sub Test()
    Dim Cell As Range
    For Each Cell In Selection
        With Cell
            .NumberFormat = "@"
            .Value = Format(.Value, "dd/mm/yyyy")
        End With
    Next Cell
End Sub
 
Upvote 0
I'll give that a go and see if it can be adapted. Thanks so much taking the trouble to get back to me.
 
Upvote 0
Will

Try formatting the dates to the format of your desire after the text to columns.

Text to columns doesn't really do formatting - with dates then it should convert what you have to 'real' dates.

They might look like 'American' dates but they're just dates, though not formatted as you want.:)
 
Upvote 0
Hi Norie

I think you've misunderstood what I'm trying to do! I'm not looking for any date formatting at all - I am turning dates TO text. The best way to understand is to try some dates out with text to columns and see what happens - then run it in code and see the difference. Or try any of the many standard formatting systems available in Excel and watch as it turns dates to a number code when in text.

Andrew Poulsom's code does the trick adnirably with an application.EnableEvents = False to prevent possible looping..
 
Upvote 0
Will

Oops, my bad - just saw the Text to columns bit.:oops:

Converting from text to dates is the normal question.:)

What code do you actually have and do you need the date as text in a specific format for something?

If you do then Andrew's suggestion of Format is a good idea.
 
Upvote 0
Thanks Norrie! Basically my work has a mail merge set up so I need the date formatted as text but giving the information as a date. They do everything manually as they are suspicious of VBA and they handle sensitive data - so we have to jump betwen a secure system and a corporate. Absolute nightmare.

Anyway, I think Andrew's code will work well. I won't bother automating the mail merge bit as it's easy enough to go to the word doc and pull it from the spreadsheet...

Thanks for taking the time as always....:)
 
Upvote 0
Eh, have you considered formatting the mail merge field in question using a switch?:)

You'd only need to do that once in the Word doc rather than formatting each date individually in Excel using VBA or a formula.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top