CODEWITHSUNDEEP
vbadateexcel
distante
distante

Reputation: 7005

How to solve mixed date formats

I'm trying to write VBA code in Excel 2010 to make some time calculations. Everything is working as I want BUT for the cell date format. The Excel Sheets were created by merging several .xlsx files generated by different PCs and a Hardware Data-logger. The problem is that some sheets had the date as mm/dd/yy hh:mm:ss AM/PM and others dd/mm/yy hh:mm:ss AM/PM, with both mixed in one file.

I tried to change everything to Selection.NumberFormat = "dd/mm/yy hh:mm;@" but some cells just don't change. I also tried this function:

Function Arreglar_Fecha()
    Dim temp As String
    temp = ""
    Do While ActiveCell.Value <> ""
       temp = ActiveCell.Value
       ActiveCell.Value = Day(temp) & "/" & Month(temp) & "/" & Year(temp) & " " & Hour(temp) & ":" & Minute(temp)
       ActiveCell.Offset(1, 0).Select
    Loop
End Function

But still, some cells changed, some did not. And what is worse, some get the day and month mixed!

http://i.imgur.com/5yGEyzi.png

enter image description here

I have access to some of the original .xlsx files and in there also wasn't able to change all the date formats.

Anyone have any idea how I can fix this?

EDIT Here I got permission for put an original Excel file Excel Data.

Upvotes: 0

Views: 6301

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149305

ActiveCell.Value = Day(temp) & "/" & Month(temp) & "/" & Year(temp) & " " & Hour(temp) & ":" & Minute(temp)

Maybe the code is reading it as text? Try this (UNTESTED)

Sub Arreglar_Fecha()
    Dim temp As String, Tmp As String
    Dim D As String, M As String, Y As String
    Dim H As String, Mn As String
    
    Do While ActiveCell.Value <> ""
       temp = Trim(ActiveCell.Value)
       
       D = Trim(Split(temp, "/")(0))
       M = Trim(Split(temp, "/")(1))
       
       Tmp = Trim(Split(temp, "/")(2))
       
       Y = Trim(Split(Tmp, " ")(0))
       
       Tmp = Trim(Split(Tmp, " ")(1))
       
       H = Trim(Split(Tmp, ":")(0))
       Mn = Trim(Split(Tmp, ":")(1))
       
       ActiveCell.Value = Format(DateSerial(Val(Y), Val(M), Val(D)) & _
                          " " & TimeSerial(Val(H), Val(Mn), 0), _
                          "dd/mm/yy HH:mm;@")
                          
       ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Trying it with a single test scenario works. The below gives you 13/08/13 05:31 

Sub Test()
    Dim temp As String, Tmp As String
    Dim D As String, M As String, Y As String
    Dim H As String, Mn As String
    
    temp = "13/8/2013 5:31"
    
    D = Trim(Split(temp, "/")(0))
    M = Trim(Split(temp, "/")(1))
    
    Tmp = Trim(Split(temp, "/")(2))
    
    Y = Trim(Split(Tmp, " ")(0))
    
    Tmp = Trim(Split(Tmp, " ")(1))
    
    H = Trim(Split(Tmp, ":")(0))
    Mn = Trim(Split(Tmp, ":")(1))
    
    Debug.Print Format(DateSerial(Val(Y), Val(M), Val(D)) & _
                " " & TimeSerial(Val(H), Val(Mn), 0), _
                "dd/mm/yy HH:mm;@")
End Sub

Upvotes: 2

pnuts
pnuts

Reputation: 59475

You will have to trace back to your source data. There is no way Excel itself knows whether 1/2/2014 for example should be the first of February or the second of January, only that it is either 41671 or 41641.

Edit In your second example, clearly 28/9/2013 17:59 is September 28. If 10/01/13 12:11:00 PM had the same formatting (perhaps came from the same file) then it is January 10. But if the formatting was different then it could be October 1. If you are seeing AMs and PMs with formatting as dd/mm/yy hh:mm;@ then some of your data is text and there is no reliable 'automatic' way to switch this to a date/time serial number without knowing the text convention (ie whether DMY or MDY), hence the need to revert to source.

Obviously 'day' values greater than 12 are actually months but that does not help much when for less than 13 it depends upon the formatting.

In addition, given your various sources, there is a risk that both the 1900 and the 1904 conventions might have been used and even possibly others also (your data logger might be on UNIX time, which starts in 1970).

Upvotes: 3

Related Questions