Interpretation of Dates going wrong

[#1]

Hi All,

I would like to use an Excelsheet for analysis, but I have some issues with the interpretation of a date field. In Excel the date records look something like this:

9/30/2014 5:06:55 PM
9/30/2014 5:12:19 PM
9/30/2014 5:12:39 PM
9/30/2014 5:15:59 PM
9/30/2014 5:58:36 PM
10-1-2014
10-1-2014
10-2-2014
10-2-2014
10-3-2014

All date-records should have the MM/DD/YYYY format, but Excel shows the first records as text. The last records are interpreted as date, but with a wrong format(DD-MM-YYYY instead of MM-DD-YYYY).

Is there an easy way to read all these records in the right format without having to change the Excel-data?

[Clever_Anjos]

LOAD
DATE(FLOOR(ALT(
  Date#(F1,'M/D/YYYY h:mm:ss TT'), // Try first format
  Date#(F1,'M-D-YYYY') // then second
)))  as Date
INLINE [
    F1
    9/30/2014 5:06:55 PM
    9/30/2014 5:12:19 PM
    9/30/2014 5:12:39 PM
    9/30/2014 5:15:59 PM
    9/30/2014 5:58:36 PM
    10-1-2014
    10-1-2014
    10-2-2014
    10-2-2014
    10-3-2014
];

[stevedark]

Hi Perry,

It is not good that your Excel spreadsheet has duff values in.  If you can fix the source it will be better all round.

You should be able to deal with what you have here though, with a bit of code something like:

LOAD

  Date(if(Alt(DateField, 0) * 1 <> 0,

        DateField,

        Date#(DateField, 'D/MM/YYYY h:mm:ss TT')),

        'MM/DD/YYYY') as CorrectDate,

The code with the Alt and the multiplication is a sure fire way to check you have a valid date field in Excel (don't worry about how it looks in Excel - this is not important), if it is a valid date you can just take it as is.  The Date# code is converting from string to a proper date, the parameter is the format of the string we are reading.  The IF block is wrapped in a Date statement which then formats the field how we want it.

Hope that helps,
Steve

[MarcoWedel]

please post an Excel sample.

thanks

regards

Marco

[#5]

thanks for the fast responses.

I know it's not very wise to use different formats, but the data comes from a machine running with a MM-DD-YYYY - format as default and my system uses a DD-MM-YYYY as default format. I have +100k of records, so I'm not eager to change the Excel-sheets.

The script of Clever works partly (the first 12 days of the month aren't recognized als shown in the pic).

It seems I'm not able to post an Excel sample. How can I do that?

regards

Perry

[MarcoWedel]

regards

Marco

[#7]

I think I just solved it. Maybe not the most elegant way of doing this, but it seems to work.

Based on the input of Clever I made the following script:

     Date(Floor(alt(

     date#(F1,'M/D/YYYY h:mm:ss TT'),

     MakeDate(YEAR(F1),DAY(F1),month(F1))))) as Date

By using the makedate function I was able to swap the month and day. Not sure if it generates any other problems along the way, but for now it works.

Thanks for your help.

[#8]

In this post the data I have to deal with.

[MarcoWedel]

Hi,

another solution could be:

Timestamp(Alt(TimeString,Timestamp#(TimeString,'MM/DD/YYYY hh:mm:ss TT'))) as Timestamp

and a preceding load

DayName(Timestamp) as Date

to get both the Timestamp as well as the Date information:

table1:
LOAD *,
    DayName(Timestamp) as Date;
LOAD StateAfter,
    TimeString,
    Timestamp(Alt(TimeString,Timestamp#(TimeString,'MM/DD/YYYY hh:mm:ss TT'))) as Timestamp,
    MsgText
FROM [https://community.qlik.com/servlet/JiveServlet/download/783209-166622/demodata.xlsx] (ooxml, embedded labels, table is [Copy ]);

hope this helps

regards

Marco

[campbellr]

Hi Perry

I have had issues with dates in excel files when converting from mm/dd/yyyy to the correct way of dd/mm/yyyy. Excel likes to take a guess at what you mean and makes changes as it sees fit, so if it sees a date with the day of 13 or more it knows that is the day and switches but if the day is 12 or less it assumes it's the month and keeps it. No messages or warnings just "fixes" it.

To fix the dates in excel prior to loading into Qlikview would be my tactic. I use a macro that sets the formula of a cell to the cell value. This by default gets excel to treat the dates as mm/dd/yyyy. I tried it with your sample data and all of your dates were treated as October.  It doesn't seem right to post excel macros on a QlikView post. So if such things offend, please look away

Ron

VBA Code block--

Sub TrimInPlace()

'  Ron Campbell

'Highlight a section of cells

'step through cell by cell trimming the contents

'Trimming will only occur on visible cells, the macro skips any rows that are hidden or not visible as a result of a filter

If ActiveWorkbook Is Nothing Then

    Exit Sub

End If

On Error GoTo ErrorHandler

Application.Calculation = xlCalculationManual

Dim currCell As Range

    If Selection.Count > 1 Then

        Selection.SpecialCells(xlCellTypeVisible).Select

    End If

    For Each currCell In Selection.Cells

        If Not Application.IsNA(currCell) Then

            currCell.Formula = Replace(Replace(Replace(Trim(currCell.Value), Chr(160), ""), Chr(10), ""), "  ", " ")

        End If

    Next

    'restore the user's calculation setting

    Application.Calculation = xlCalculationAuto

    Exit Sub

ErrorExit:

    'restore the user's calculation setting

    Application.Calculation = xlCalculationAuto

    'the ErrorHandler code should only be executed if there is an error

    Exit Sub

ErrorHandler:

        Debug.Print Err.Number & vbLf & Err.Description

        Resume ErrorExit

End Sub