VBA code to convert a cell content formatted as text to dateformat - from danish dates to danish dateformat

Hi Experts

I have been searching for a solution to this problem on the internet - but for reasons, I do not understand no solutions seems to work

In each of my cells in a range

cell A2 formatted as text and value is 11-12-2014  which equals December 11th 2014
Cell A3 formatted as text and value is 04-12-2014 which equals December 4th 2014

how can these be converted to the Danish shortdate format 11-12-2014

I need this done in VBA

I have tried the following solutions

Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
    c.Value = CDate(c.Value)
Next c

Open in new window


Sub TilDato()
     For Each c In Selection.Cells
        c.Value = Replace(c.Value, ".", "-")
        c.NumberFormat = "dd-mm-yyyy"
     Next c
 End Sub

Open in new window


as well as a couple of recorded macros.

a couple of solutions Work fine, when I record the macro, but when I run the solution on more than one cell my dates are converted to

12th november 2014 (12-11-2014)
12th april 2014 (12-04-2014)

How can I Work around that VBA wants to speek English?
LVL 4
JorgenAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ejgil HedegaardConnect With a Mentor Commented:
I have a danish setup and Cdate converts the text date 11-12-2014 to the correct date, local format, serial number 41984 as expected.
The text date is inserted in the cell with a leading ' to set it as text '11-12-2014 before macro run.

But when the cell format is text, the date is converted to another text 12/11/2014 US format.
Setting the cell format to General before conversion solve the problem, and CDate convert to local format.

Dim c As Range
For Each c In ActiveSheet.UsedRange.Columns("A").Cells
    c.NumberFormat = "General"
    c.Value = CDate(c.Value)
Next c

Open in new window

0
 
SimonCommented:
Try using .Value2 instead of .Value
See the MSDN page for Value2
0
 
Rgonzo1971Commented:
Hi,

pls try with

c.NumberFormatLocal

Regards
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Rory ArchibaldCommented:
CDate should work as long as the regional settings of the machine on which you run it are set to use a dd/mm/yyyy format.
0
 
JorgenAuthor Commented:
@Rory
My regional setting is dd-mm-yyyy as the format I want Excel to show, so even if it should Work, it does not.

@Rgonzo1971
It seems like c.NumberFormatLocal Works, but the format is kept as userdefined format and not date format. I want to keep the dateformat, as I use that in calculations in other macros.

@SimonAdept
I read the MSDN you referred to, but I can not see how that solves my problem. It might be, that I do not see the potential, so please suggest code
0
 
JorgenAuthor Commented:
It seems like my format is still something else than a date. Even if I try to format from userdefined (that is setup as date) to dateformat, it seems like the content of the cell is something else than a date. As soon as I activate the cell content and deactivate.
0
 
SimonConnect With a Mentor Commented:
On my UK system, your text dates are correctly converted to UK dates dd/mm/yyyy when I put the text in. Regional settings and dates are still an occasional headache to me. If you've specifically overridden the date format rather than gettting it automatically set as part of your regional/language settings, I'd be suspicious that this is the root cause.
Danish.pngI haven't had to use Value2 for a long time, but have done in the past to get around the way Excel interprets ambiguous date-like strings as US dates.

I'd suggest trying these 3 variations
Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
    c.Value = CDate(c.Value2)
Next c

Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
    c.Value2 = CDate(c.Value2)
Next c

Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
    c.Value2 = CDate(c.Value)
Next c

Open in new window

0
 
JorgenAuthor Commented:
Hi all,

With a little change did Eigils code Work fine (I need to look more into your previous answers to other experts :-). I will try Simons 3 suggestions later, to give fair points

Eigil the code stopped after the header cell, when I ran your code, while it is running fine, if I skip the header. Any suggestions to solve that, or do you have similar problems?

regards

Jørgen
0
 
Ejgil HedegaardCommented:
Only a text that looks like a date can be converted using CDate, any other text will fail, giving a run time error "type mismatch".

Use IsDate to check if the text can be converted.

If you want to know which texts converts to a date, set a mark in column B when successful, so the code could be like this
Sub ConvertToDate()
Dim c As Range
For Each c In ActiveSheet.UsedRange.Columns("A").Cells
    If IsDate(c.Value) Then
        c.NumberFormat = "General"
        c.Value = CDate(c.Value)
        c.Offset(0, 1) = "x"
    End If
Next c
End Sub

Open in new window

0
 
JorgenAuthor Commented:
Both the solutions from Eigil and the last 2 from Simon worked fine. The only reason for giving Eigil a Little more points is that his code included conversion to a date range as well.
0
All Courses

From novice to tech pro — start learning today.