11 Replies Latest reply: Dec 21, 2012 7:30 AM by Anne Duffy RSS

    Subtract Dates and Times field

      Hi Experts

       

      I hope you can help :

       

      I am loading data in from an Excel spreadsheet , I wish to be able to calculate the Turnaround time of a Task.

       

      I have the following Columns

       

      hh:mm            dd/mm/yyyy          hh:mm           dd/mm/yyyy    

      [Start Time]     [Start Date]          [End Time]     [End date]

      8:00               1/1/12                    10:00               1/1/12

      8:00               1/1/12                    11:00               1/2/12

      8:00               1/1/12                    11:30              1/2/12

       

      What I'd like is to have nother column that does the calc - so see below with desired results in Hours:Minutes Format

       

      hh:mm            dd/mm/yyyy          hh:mm           dd/mm/yyyy          hh:mm

      [Start Time]     [Start Date]          [End Time]     [End date]               [turnaround]

      8:00               1/1/12                    10:00               1/1/12                   2:00

      8:00               1/1/12                    11:00               1/2/12                   27:00

      8:00               1/1/12                    11:30              1/2/12                    27:30

       

      Not sure if its best to do as part of the load or if we should calc in a Chart

       

      Can anyone advise how I may do this please ?

       

      Thanks


      A

        • Re: Subtract Dates and Times field
          Gysbert Wassenaar

          It's best to calculate it in the script as part of the load. Depending on how the times and dates are stored in excel you need to determine how you can calculate the turnaround. Maybe it's possible to simply add Start Date and Start Time to get a timestamp. If you're not that lucky you may need to use the timestamp# function, e.g. something like timestamp#( [Start Date] & ' ' & [Start Time], 'DD/MM/YYYY hh:mm'). You can calculate the turnaround with the interval function. Try interval([End Date]+[End Time]-[Start Date]-[Start Time],'hh:mm') as turnaround

            • Re: Subtract Dates and Times field
              Time receivedDate received by RCMTValue Time approved / declinedDate closedCorrect HoursQlikview HoursDiff
              10:00:0015/10/2012      97,295.00 1515782208:50:0020/10/2012118.5138.520
              10:00:0017/10/2012 1,415,779.63 Limit Ext14:45:0023/10/2012148.45168.4520
              10:00:0018/10/2012      89,033.00 1092537214:13:0018/10/20124.1324.1320
              10:00:0018/10/2012      30,000.00 1585555014:50:0024/10/2012148.5168.520

               

              Hi Gysbert

               

              Thanks for the above - I was just testing it - I loaded it in the script as :

                

              interval

              ([Date closed]+[Time approved / declined]-[Date received by RCMT]+[Time received],'hh:mm') as Turnaround

               

                

               

               

              Then in the Table just sum(Turnaround) - It turns out the calc is incorrect by exactly 20 everytime .

              Do you know why ?

               

              Thanks
              Anne

               

               

               

               

              • Re: Subtract Dates and Times field

                Hi Gysbert

                 

                The above is how I requested - thanks a mill

                 

                Just wondering is it possible to format the same as days - see below

                 

                Thanks so much


                Anne

                 

                 

                hh:mm            dd/mm/yyyy          hh:mm           dd/mm/yyyy          hh:mm                    Days

                [Start Time]     [Start Date]          [End Time]     [End date]               [turnaround]             [ turnaround]    

                8:00               1/1/12                    10:00               1/1/12                   2:00                    0.08

                8:00               1/1/12                    11:00               1/2/12                   27:00                1.125

                8:00               1/1/12                    11:30              1/2/12                    27:30                   1.145

                • Re: Subtract Dates and Times field

                  Hi Gysbert

                   

                  The above is how I requested - thanks a mill

                   

                  Just wondering is it possible to format the same as days - see below

                   

                  Thanks so much


                  Anne

                   

                   

                  hh:mm            dd/mm/yyyy          hh:mm           dd/mm/yyyy          hh:mm                    Days

                  [Start Time]     [Start Date]          [End Time]     [End date]               [turnaround]             [ turnaround]    

                  8:00               1/1/12                    10:00               1/1/12                   2:00                    0.08

                  8:00               1/1/12                    11:00               1/2/12                   27:00                1.125

                  8:00               1/1/12                    11:30              1/2/12                    27:30                   1.145