NetWorkDays calculation with add'l logic?

[#1]

I haven't found anything in documentation pdfs or posts that address my current assignment, so I'm asking here. These are the things I'm working with:

• Calculate the open time for requests using the formula ReloadTime() - incident.open
• If the SEV field is a "3" or "4", do NOT count open time during Saturday and Sunday
• A request can be submitted at any time during the week Sun-Sat, and sometimes can be open for more than 8 days.
• Results should be in dd:hh:mm format

I can get results using NetWorkDays in conjunction with Frac,Interval,Time, etc as suggested in other posts on here but it's never accurate; and I'm figuring it's because of the fact that requests can be opened during the weekend.

Thank you for your assistance.

[avinashelite]

hi

try like this:

if(SEV=3 or SEV=4, networkingdays(ReloadTime() - incident.open), ReloadTime() - incident.open)

[#3]

Thank you for your prompt reply, Avinash, but your proposed expression is missing some things I need:

• While it addresses the SEV constraints in calculation, I still need something that accounts for requests made during the weekend as well as the dd:hh:mm format
• "networkingdays" doesn't seem to be recognized by QV. NetWorkDays() is but it uses different syntax than your expression

Any other suggestions?

[maxgro]

try if this (and Gysbert's link) can help you

Re: Calculate Elapsed Time Nett

[#5]

The one discussion you sent me about "Calculate hours between two Date/Time strings" had something I was looking for - simplicity. Particularly this post here:

=Interval(

          (RangeMin(frac(Closedate), MakeTime(18))

          - RangeMax(frac(Logdate), MakeTime(8)))

          + (NetWorkDays(Logdate, Closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)

Just an expresssion using what QV has already. No calendar tables, nothing fancy. Fortunately, I don't need such granularity for the business hours so I don't think I need the MakeTime part. This expression works unless the request is open through the weekend, which isn't what I need:

if(num(WeekDay(incident.sys_created_on))>4,

   1-NetWorkDays(timestamp(incident.sys_created_on),timestamp(ReloadTime())) + Frac(timestamp(incident.sys_created_on)-Timestamp(ReloadTime(), 'hh:mm'))

,

    NetWorkDays(timestamp(incident.sys_created_on),timestamp(ReloadTime())) + Frac(timestamp(incident.sys_created_on)-Timestamp(ReloadTime(), 'hh:mm')))

Any further help is appreciated!

[avinashelite]

Hi David,

Can you please share your app??

[#7]

Sure, here's the trial app I've been working on.