locked
Excel converting date strings to date serials based on locale RRS feed

  • Question

  • Hello

    We have an Excel report hosted on a Web Portal. This Excel report imports data from some csv files containing dates in dd/mm/yyyy format.

    Our clients in US are facing issues in using this report as based on their locale settings, Excel is trying to convert dates in csv file as mm/dd/yyyy.

    We already tried these options:

      • configured those column as English (Singapore)
      • used date formats starting with or without *
      • formatted columns as text (with the intention to parse date text ourself)

    but despite all of above settings, Excel still tries to calculate date serials for the dates in csv files.

    What I am looking for is:

    1. (preferable) Some configuration which makes Excel to always treat date text as dd/mm/yyyy (irrespective of Locale)
    2. Or, do not parse (i.e. convert into date serial), even if Excel thinks the values are actually date.

    Limitations: We can not change Locale or the source csv files, but free to append the Excel report (including associated VBA code)

    Thanks - Mukesh


    Thanks Mukesh

    Tuesday, September 25, 2012 8:52 AM

Answers

  • Thanks Javed.

    We tried this approach earlier by writing something like :

    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo :=Array(Array(1, 2), Array(2, 1), Array(3, 1))

    Where 2 of Array(1,2) is representing Text type (which we used for dates), and that gave us correct date string which we parsed later.

    I was looking for a more elegent solution, something at a Workbook configuration level so that we don't have to put too much muddy code around.


    Thanks Mukesh

    • Marked as answer by 许阳(无锡) Thursday, October 4, 2012 8:07 AM
    Wednesday, September 26, 2012 8:06 AM

All replies

  • can you put an example of the date in your report and show us how it changes back to the region?


    Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or "Mark as Answer" if it solves your question. This will help to contribute to the forum.

    Tuesday, September 25, 2012 2:36 PM
  • If you convert the date to text, the IsDate function still reports the value as a date.

    However, if you insert an "*" in front, it reports IsDate as false.
    You can still easily convert to date by VBA or UDF function: cdate(Mid(ActiveCell.Value,2))

    Tuesday, September 25, 2012 2:42 PM
  • By the time data gets imported to Excel, it is already converted to date serials. To if you format is text, what you will see is a number.

    Suppose if you have following dates in csv (in dd/mm/yyyy format):

    12/05/2012

    04/06/2012

    13/05/2012

    If someone is opening the Excel report in a country with dd/mm/yyyy, all the above dates will be parsed correctly (into date serials)  as 12-May-2012, 04-Jun-2012 and 13-May-2012. If the Excel report is opened in a country with locale mm/dd/yyyy. first two dates will be parsed into date serials (although incorrectly) to 05-Dec-2012, 06-Apr-2012 and third one will remain as string as Excel could not validate this string as date.

    I've to instruct Excel that the dates should always be considered as dd/mm/yyyy irrespective of system locale. For background, our Excel report is importing csv data from an intranet URL using import ranges (using an automated code).


    Thanks Mukesh

    Wednesday, September 26, 2012 2:39 AM
  • http://www.excelforum.com/excel-general/643091-date-formats-incorrect-when-vba-opens-workbook.html

    has the solution for this.


    Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or "Mark as Answer" if it solves your question. This will help to contribute to the forum.

    Wednesday, September 26, 2012 3:05 AM
  • The link assumes that you are able to get dates in string format (which makes it very easy to parse dates yourself, because you know source data date format).


    Thanks Mukesh

    Wednesday, September 26, 2012 4:24 AM
  • Hi Mukesh,

    Thanks for posting in the MSDN Forum.

    This is the csv file:

    duty date,member
    30/9/2012,Forrest
    1/10/2012,Tom
    2/10/2012,Yoyo
    3/10/2012,Quist
    4/10/2012,Leo

    I think following VBA will work (In this code "Microsoft VBScript Regular Expressions 5.5" need be implement):

    Sub test()
        Dim WS As Worksheet
        Dim i As Integer
        
        Set WS = ActiveWorkbook.ActiveSheet
        i = 2
        While WS.Cells(i, 1).Value <> ""
            WS.Cells(i, 1).Value = GetDate(WS.Cells(i, 1).Value)
            i = i + 1
        Wend
        
        Columns("A:A").Select
        Selection.NumberFormat = "m/d/yyyy;@"
        
        Set WS = Nothing
    End Sub
    
    Function GetDate(ByVal Str As String) As Date
        Dim Regx As RegExp
        Dim Matchs As Variant
        
        Set Regx = New RegExp
        With Regx
            .Global = True
            .Pattern = "^(\d{1,2})/(\d{1,2})/(\d{4})$"
        End With
        
        Set Matchs = Regx.Execute(Str)
        
        GetDate = CDate(Matchs(0).SubMatches(1) & "/" & Matchs(0).SubMatches(0) & "/" & Matchs(0).SubMatches(2))
    End Function

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us


    Wednesday, September 26, 2012 5:57 AM
  • Dear Mukesh,

    Have you tried with Text To Column feature of excel under data tab.I think that will do...

    This feature allows you to specify format column by column.Here is the step list...

    1.Open the csv in notepad/wordpad and save as txt file.[This is required because excel automatically converts csv file and does not give you chance to specify format.]

    2.Now open an excel instance and click OPen for Office menu/or from File if 2003

    3.The text import wizard will appear.1st page asks about the fixed wodth or delimited.select apprpriate.The 2nd page asks you delimiter or allows you to set field width [if fixed width in earlier page selected].

    4.In the 3rd page you get option to specify format.Choose date and in the right drop down choose mdy or dmy as appropriate.

    5.After that you may click finish.But pls follow the offline help of excel for detail about this helpful feature

    Here is the screen shot.

     


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, September 26, 2012 6:52 AM
    Answerer
  • Thanks Javed.

    We tried this approach earlier by writing something like :

    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo :=Array(Array(1, 2), Array(2, 1), Array(3, 1))

    Where 2 of Array(1,2) is representing Text type (which we used for dates), and that gave us correct date string which we parsed later.

    I was looking for a more elegent solution, something at a Workbook configuration level so that we don't have to put too much muddy code around.


    Thanks Mukesh

    • Marked as answer by 许阳(无锡) Thursday, October 4, 2012 8:07 AM
    Wednesday, September 26, 2012 8:06 AM
  • Hi Mukesh,

    It seems that your solution will better enough on your issue. And Learning's solution is more easier to use. Learing's reply can be a good answer without considering whether use code.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us


    Thursday, September 27, 2012 6:23 AM