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
276 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

776 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