Solved

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

Posted on 2015-02-09
10
267 Views
Last Modified: 2016-02-10
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?
0
Comment
Question by:Jorgen
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40598074
Try using .Value2 instead of .Value
See the MSDN page for Value2
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40598075
Hi,

pls try with

c.NumberFormatLocal

Regards
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40598096
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
 
LVL 4

Author Comment

by:Jorgen
ID: 40598201
@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
 
LVL 4

Author Comment

by:Jorgen
ID: 40598244
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Assisted Solution

by:Simon
Simon earned 200 total points
ID: 40598512
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
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 300 total points
ID: 40599372
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
 
LVL 4

Author Comment

by:Jorgen
ID: 40600218
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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40601230
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
 
LVL 4

Author Closing Comment

by:Jorgen
ID: 40623870
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now