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

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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

766 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