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

# 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

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)

if(isnull([Date closed]),

) as OpenTime,

• ###### Subtract and Multiply Date Time as 8 hours not 24

Thanks Christoph

Thats perfect

• ###### Re: Subtract and Multiply Date Time as 8 hours not 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...

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)