Subtract and Multiply Date Time as 8 hours not 24

[#1]

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

[christophebrault · Accepted solution]

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,

[#3]

Thanks Christoph

Thats perfect

[christophebrault]

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 ?

[#5]

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)