1

When I open a file which contains a column with dates, Excel does not recognize these cells as dates. The dates in the cells looks like : 31-Mar-16 23:51

I have tried with text-to-columns and datevalue although this does not solve the problem. I also read this post:

String was not recognized as a valid date time

Still I have the problem.

The file is coming from the UK. My computer regional setting is Dutch for language and US for keyboard. Even when I set my regional setting to UK and re-open the file, the date is still not recognized. Excel sees it as text.

With some text editing I can replace 31-Mar-16 23:51 by 31-Mar-16 and then replace Mar by 3. In that case the problem is solved although this method is not very efficient since I receive these files periodically.

Does anyone know a better way to solve this?

Thanks!

Community
  • 1
  • 1
Marcel
  • 115
  • 2
  • 15
  • 1
    When the Excel you received really contains **dates** you shouldn't have problems opening it - because dates are internally stored as numbers and presented to you using your PC locale settings. In the case your original file contains dates stored as texts you will need to search/replace month names as well as date & time seperators to match your current locale (or any temporarily chosen locale). In hard cases one more step could include to use `=VALUE()` to sanitize the final string into a real date. – MikeD Apr 04 '16 at 11:48
  • Thanks Mike. Indeed I suppose the file contains dates stored as text. Although for february there is no problem since february is Feb, both in Dutch as in English. For february the dates are recognized as dates. – Marcel Apr 04 '16 at 12:22
  • 1
    I would be interested in knowing what the VBA DateValue or CDate returned (not the worksheet DATEVALUE). –  Apr 04 '16 at 12:26
  • @Jeeped. Promising idea. Evaluating `?Cdate("31-Mar-16 23:51")` in the immediate window yields `3/31/2016 11:51:00 PM` – John Coleman Apr 04 '16 at 12:28
  • Well, VBA is very EN-US-centric but I don't know how it will handle EN month names on a DA-DK (...?) system. –  Apr 04 '16 at 12:33
  • I receive a compile error at 23 in the string : Expected:list sepeartor or ) – Marcel Apr 04 '16 at 12:40
  • PS my system is NL-US – Marcel Apr 04 '16 at 12:41
  • @Marcel, is Cdate is working on the cells? for ex: in your immediate window ?Cdate(Range(your date cell)) returns what – Karthick Gunasekaran Apr 04 '16 at 14:56

2 Answers2

0

I suspect that the month abbreviations are not being recognized due to Locale settings. If that is the case, select the miscreant cells and run this macro:

Sub DateFixer()
    Dim r As Range, t As String
    Dim dy As Long, mont As Long, yr As Long, hr As Long, minn As Long
    For Each r In Selection
        t = r.Text

        ary = Split(t, " ")
        bry = Split(ary(0), "-")
        cry = Split(ary(1), ":")

        dy = CLng(bry(0))
        mont = mnth(bry(1))
        yr = 2000 + CLng(bry(2))

        hr = CLng(cry(0))
        minu = CLng(cry(1))
        r.NumberFormat = "General"
        r.Value = DateSerial(yr, mont, dy) + TimeSerial(hr, minu, 0)
    Next r
End Sub

Public Function mnth(st) As Long
    Dim sMth

    sMth = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
    With Application.WorksheetFunction
         mnth = .Match(st, sMth, 0)
    End With
End Function

Note that the macro can be adapted to any language's abbreviations by simply changing the array in the UDF().

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thank you both for your solutions. Both solutions work fine, although it still needs a workaround. Do I understand correct that this is necessary since my file does not contain date-fields? I was hoping I could solve the problem by changing my pc-settings in a certain way. I will upload my file to show you. Thanks in any case since the macro is pretty userfriendly. – Marcel Apr 05 '16 at 09:00
  • ps: I don't see how I can upload an Excel file. – Marcel Apr 05 '16 at 09:08
0

To convert months without VBA, you can use =FIND() and =MID() functions like so ...

enter image description here

Use further =LEFT(), =MID(), =RIGHT() to decompose your source date and compose a correct date according to your rules.

See also Excel using date/time with multiple different region formats. VB? Formulas?

Community
  • 1
  • 1
MikeD
  • 8,861
  • 2
  • 28
  • 50