5 Replies Latest reply: Feb 18, 2016 11:29 PM by Aishwarya Shetty RSS

    Timestamp Problem

    Aishwarya Shetty

      Hi,

       

      I have some problem with the timestamp function that I am using. I have two excel sheets for the month of Jan 2016 and Dec 2015. The date column (in my case, "Incident created Date and Time") is a timestamp in both the sheets. I need to retrieve month and year from it. The timestamp formula that I have used works fine with Jan 2016 data but not with Dec 2015 data. I am attaching the qvf and the excel sheets. Please help

        • Re: Timestamp Problem
          Vijit Bhargav

          Hello Aishwarya,

           

          No sure whether you've found what you were looking for, but following expression can be tried:

           

          =Date(Timestamp(Timestamp#([Incident created Date and Time],'MM/DD/YYYY hh:mm tt'),'MM/DD/YYYY hh:mm tt'),'MMM-YYYY')

           

          Regards,

          Vijit

            • Re: Timestamp Problem
              Aishwarya Shetty

              Hi Vijit,

               

              Thanks for the reply. I tried this but even this formula works only for Jan 2016 data and not for Dec 2015 data.

                • Re: Timestamp Problem
                  Kaushik Solanki

                  Hi,

                   

                  Try this script.

                   

                   

                   

                  LOAD [Incidents #],

                       ([Incident created Date and Time]),

                       [Incident Resolved Date and Time],

                       [Incident Closed Date],

                       Date(Date#(left([Incident created Date and Time],10),'MM/DD/YYYY')) as Date,

                       Month(Date(Date#(left([Incident created Date and Time],10),'MM/DD/YYYY'))) as Month

                  FROM

                  [RCA Report Dummy.xlsx]

                  (ooxml, embedded labels, table is [Dec-15]);

                   

                   

                   

                   

                  LOAD [Incidents #],

                       [Incident created Date and Time],

                       [Incident Resolved Date and Time],

                       [Incident Closed Date],

                       Date(Date#(left([Incident created Date and Time],10),'MM/DD/YYYY')) as Date,

                       Month(Date(Date#(left([Incident created Date and Time],10),'MM/DD/YYYY'))) as Month

                  FROM

                  [RCA Report Dummy.xlsx]

                  (ooxml, embedded labels, table is [Jan-16]);

                   

                  Regards,

                  Kaushik Solanki

                  • Re: Timestamp Problem
                    Stefan Wühl

                    Your Dec data seems to use a different white space character between hh:mm and tt than in Jan data:

                     

                    timestamp#( Replace("Incident created Date and Time",chr(160),' '), 'MM/DD/YYYY hh:mm tt') as  "Incident created Date and Time",

                     

                    So either use replace() function, correct your source data, or use left() to only read in the date part.