Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
Thanks Christoph
Thats perfect
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 ?
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)