Date and Hour formula with criteria match

[savetrees]

Hi,

I am trying to setup a SLA based criteria with formula based instead of too many support columns, can anyone help ... i have attached the sample file

[protonLeah]

Need more detailed explanation, and before and after examples.

[oeldere]

A agree with protonLeah that you provide not enough infomation.

Nevertheless I gave it a shot with an pivot table.

See the attached file.

[savetrees]

ProtonLeah & Oeldere,

Thanks for your response and sorry for incomplete information.

and Here is something i am looking to create ... .. If you see Column G to I, Thresholds/Timelines are created based on Severity Criteria. Which means from Column B to D ... if column B matches with column H .. then if column C & D falls between the timelines provided in column I .. then count should be calculated otherwise NULL. Hope i did not complicated.

Meanwhile, there is one more addition to this condition is between column C & D ... i need to ignore the timing between 3:30 AM to 9:30 AM .. as its called as holiday time. please advice.

Thanks

[savetrees]

ProtonLeah & Oeldere,

Thanks for your time and i have got the solution and its working fine ... .. please check the attached. If you feel this formula is too complex and if this can be optimized, then i would like to proceed with it.

Thanks

[oeldere]

Thanks for the reply, and sharing your solution on the forum.

[savetrees]

Hi Oeldere,

I have encountered with one more challenge, can you please lookinto this ... .. i need to add the holiday calendar for 2 locations ... which i can add easily .. but tricky part is ..

This is 24 hour SLA calendar ... ..

My first location - starts 9:30 AM Ends 6:30 PM
My second location - starts 6:30 PM Ends 3:30 AM
Between 3:30 AM till 9:30 AM its non-business hours

Well if i add the holiday calendar for 2 location, i will have the complexity in timing ... for example 21st Aug 2014 is holiday in first location, but second location is functioning ... in that case, i have to consider the ticket SLA timing from 6:30 PM till 3:30 AM ...

[oeldere]

You get better help on your question, if you remove the status from solved to natural.

[savetrees]

Thanks Oeldere ... i have changed the status now.