4 Replies Latest reply: Apr 26, 2012 8:46 AM by Anne Duffy RSS

    Subtract and Multiply Date Time as 8 hours not 24

      Hi Guys

       

      I have an Excel doc that feed into QV,

       

      When the column  Date closed is null means that a case is still open, if this is the case I wish to see how long its been open so I subtract the date and time away from Now. See Calc below

       

      if(isnull([Date closed]),interval( time(now(),'hh:mm:ss.fff') - ([Date received by RCMT]+[Time received]) )) as OpenTime,

       

      The only problem is that thsi is counting every day as 24 hours , I wish to count them as 8 , can anyone help me on this please ?

       

      Thanks


      A

        • Subtract and Multiply Date Time as 8 hours not 24
          Christophe Brault

          Hi,

           

          You could say that 24 hours = 8 hours and add the rest, it can gives you an approach...

           

          For this, use div() and mod().

           

          For example, if you have 50 hours :

           

          =(div(50,24)*8)+mod(50,24)

           

          in your expression :

           

           

          if(isnull([Date closed]),

          (div(interval( time(now(),'hh:mm:ss.fff') - ([Date received by RCMT]+[Time received]) ),24*8)+mod(interval( time(now(),'hh:mm:ss.fff') - ([Date received by RCMT]+[Time received]) ),24)

          ) as OpenTime,

          • Re: Subtract and Multiply Date Time as 8 hours not 24
            Christophe Brault

            Be careful with this expression because if mod(interval( time(now(),'hh:mm:ss.fff') - ([Date received by RCMT]+[Time received]) ),24) >8, it will add more than 8...

             

            I'm just thinking about that.

            for example (div(50,24)*8)+mod(50,24) = 18 and (div(47,24)*8)+mod(47,24) = 31

             

            Consider this expression where X is your Time :

             

            (div(X,24)*8)+mod(X,24)

             

            if(isnull([Date closed]),

                if(mod(X,24)>8,

                      (div(X,24)*8)+(8-(24-mod(X,24)),

                      (div(X,24)*8)+mod(X,24))

            ) as OpenTime,

             

            This expression should fix the problem.

             

            I suppose that the 8 hours represent the time when your society is opened. To have more accuracy, we perhaps can take open hour and close hour.

             

            Do you have a sample of your time fields ?

              • Subtract and Multiply Date Time as 8 hours not 24

                Hi Christoph

                 

                The times are taken from an excel doc and they are typed in '8:00:00' and formatted as time

                 

                What I actually did was take the original amended script and load that in addition to how it stood,

                 

                so I had OPENTIME ( that worked for greater than 1day) and left the original calc that worked for less than 1 day but not to calculate as 8 hours, and in my chart as the demension I have an if statement that I think works:

                 

                Thanks a mill


                A

                 

                if

                (OpenTime>2,OpenTime1OpenTime)