Hi all,
I need an help to calculate SLA by considering office hours only. As of now i'm using this formula =WORKDAY(A2,2,DATE(YEAR(A2),{12,13},{25,1}))+MOD(A2,1) which is calculating non-working hours also.
My office timings are from 09:00AM to 18:00PM.
Any help would be appreciated!!
Elango
So your formula is adding 2 working days to a date/time in A2 (excluding some holidays)
How do you want that to be different? It would be helpful if you could give some examples:
can A2 be outside working hours? Can you give required results for some sample values?
Audere est facere
Hi Daddylonglegs,
When i posted yesterday my page was expired, so only i started new thread.
can A2 be outside working hours? Yes it will be anytime i receive ticket.
Can you give required results for some sample values - Here you go,
Received time : 8/29/2014 12:39:10 AM
SLA time : 9/2/2014 12:39:10 AM(It was technically wrong which is whole 48 hours)
I want to calculate SLA only from 09:00AM to 18:00PM
OK for that example what do you expect the result to be, 9/2/2014 at 09:00?......or should it be 9/1/2014 at 18:00?Originally Posted by Elangovan89
You can try this version to get the former
=WORKDAY(A2-1,3,DATE(YEAR(A2),{12,13},{25,1}))+MEDIAN(MOD(A2,1),"9:00","18:00")
Hello,
That was a gem thanks a lot!!!
Could you please tell me where i can learn formulas with different scenarios? It will be great if I am able to get solution on my own.
Any suggestions?
Elango
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks