12 Replies Latest reply: May 27, 2015 8:50 PM by Ron Campbell RSS

    Interpretation of Dates going wrong

    perry van der meijden

      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?

        • Re: Interpretation of Dates going wrong
          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
          ];
          
          • Re: Interpretation of Dates going wrong
            Steve Dark

            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

            • Re: Interpretation of Dates going wrong
              Marco Wedel

              please post an Excel sample.

               

              thanks

               

              regards

               

              Marco

              • Re: Interpretation of Dates going wrong
                perry van der meijden

                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). Knipsel.PNG

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

                 

                regards

                Perry

                • Re: Interpretation of Dates going wrong
                  perry van der meijden

                  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.

                  • Re: Interpretation of Dates going wrong
                    Ron Campbell

                    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

                    • Re: Interpretation of Dates going wrong
                      perry van der meijden

                      Thanks for all. For now I'll stick to my own solution ;-) : not the most elegant solution, but (with my limited knowledge about scripting) for me easy to understand.

                       

                      regards Perry