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

[wilcowoods]

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

 

[wilcowoods]

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.

I hadn't! But it sounds like a good idea (i don't know much about mail merge). I've just finished the sub, though, and it goes through the whole sheet and inserts columns, headings and formulae for a data range that changes in size and form, depending on a fair few variables, as well as re-formatting bits and bobs for the merge. So far seems to work a treat - and I think I've trapped most possible user-errors. The good thing is now we just have to push a button and then go straight to merge and print! I'll have to investigate the switch you mention though ..

 

[Peter_SSs]

Will

Another possible approach. Similar to Andrew's but not needing to actually cycle through row-by-row.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ConvertDates()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("F2", Range("F" & Rows.Count).End(xlUp))<br>        .EntireColumn.Insert<br>        .NumberFormat = "@"<br>        <SPAN style="color:#00007F">With</SPAN> .Offset(, -1)<br>            .FormulaR1C1 = "=Text(RC[1],""dd/mm/dd"")"<br>            .Offset(, 1).Value = .Value<br>            .EntireColumn.Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[wilcowoods]

Will

Another possible approach. Similar to Andrew's but not needing to actually cycle through row-by-row.


Sub ConvertDates()
Application.ScreenUpdating = False
With Range("F2", Range("F" & Rows.Count).End(xlUp))
.EntireColumn.Insert
.NumberFormat = "@"
With .Offset(, -1)
.FormulaR1C1 = "=Text(RC[1],""dd/mm/dd"")"
.Offset(, 1).Value = .Value
.EntireColumn.Delete
End With
End With
Application.ScreenUpdating = True
End Sub

Hullo Peter

Nice approach too, especially as you don't have to worry about looping etc.

Because there are a number of different columns with different formats I've established a range using the usual:

FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

Set VarDataRange = Range(Cells(1, FinalCol), Cells(FinalRow, 1))

and I've used:

If IsDate(cell.Value) And cell.NumberFormat <> "@" Then
With cell
.NumberFormat = "@"
.Value = Format(.Value, "dd/mm/yyyy")
End With
End If
Next cell

..to convert cells that aren't already text and that are date formatted. I've used enableevents = false and exit sub at appropriate points to avoid looping but my coding is still clumsy and spaghetti-like!

 

[ashgaroth]

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.

Formatting the date in the Word document does absolutely nothing. Obviously Microsoft care little for those us that use their software and use dates in the way that they were designed, not the ridiculous American way

 

[Norie]

Formatting the date in Word does work, as long as you have 'real' date values in Excel.

 

[ashgaroth]

Formatting the date in Word does work, as long as you have 'real' date values in Excel.

I do, and it doesn't work

 

[Norie]

What exactly did you try?

 

[pratmacro]

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.

hi i have declare variable as Dim fdate as date and in first cell i have written date as 4/5/2015. i want date format should me mm/dd/yyyy but not able to do output should be 04/05/2015 but zero not pulling. please advice


fdate = sh1.Cells(1, 1).Value
fdate = Format(fdate, "mm/dd/yyyy")

 

[Andrew Poulsom]

What's your short date format in Windows Control Panel?

 

[ashgaroth]

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!

I have been trying to this for yonks and have never found anything useful on any Excel forum. Today,I tried something different and it appears to work.

This was my problem:
txtReviewDate = Format(Range("BG" & Cnt), "dd/mm/yyyy")
As you can see, I am trying to get the date frim the range into txtReviewDate. This created a problem when the date was ambiguous.
Example - 6th July 2017
The date on my spreadsheet is 06/07/2017 but, using the above code, txtReviewCode became 07/06/2017.

I have now sorted this by using the following code:
I send the date to a function, to verify that it is, indeed, a date. I then format it in the function, if necessary, and return it in a second variable (FormattedDate)
txtReviewDate.Text = FormattedDate.