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 ?
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)
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)
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.
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:
