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
254 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:SimonAdept
ID: 40598074
Try using .Value2 instead of .Value
See the MSDN page for Value2
0
 
LVL 48

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

708 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

16 Experts available now in Live!

Get 1:1 Help Now