Need formula in calculating SLA

Helly Techy 321,

It always helps if you give examples. I'm not sure if you want to determine whether SLA has been met given priority in D and the time range defined by columns G and H........or whether you want to get a future date/time based on the priority and one of those dates - please describe the result required and which parts of the data you expect to use

Your date/time values seem to be in text format, which introduces an extra complication....but you can use this formula in I2 copied down to convert to real date/times, work out the difference in business days and then compare against the correct SLA based on priority

=IF(NETWORKDAYS(DATE(100+LEFT(G2,2),MID(G2,4,2),MID(G2,7,2)),DATE(100+LEFT(H2,2),MID(H2,4,2),MID(H2,7,2)))-1+RIGHT(H2,8)-RIGHT(G2,8)<=IF(D2=3,1,IF(D3=4,2,5)),"SLA Met","SLA Not Met")

Assumptions:

All date/times are Monday to Friday, as per your examples,
All priorities are 3, 4 or 5

If you want to exclude holiday dates too then list your holidays and name the range Holidays and then alter formula as follows:

=IF(NETWORKDAYS(DATE(100+LEFT(G2,2),MID(G2,4,2),MID(G2,7,2)),DATE(100+LEFT(H2,2),MID(H2,4,2),MID(H2,7,2)),Holidays)-1+RIGHT(H2,8)-RIGHT(G2,8)<=IF(D2=3,1,IF(D3=4,2,5)),"SLA Met","SLA Not Met")

See attached using first formula

Thanks a ton daddylonglegs. The formula is working like a Charm. I appreciate your efforts.

I added one more Column " I ". Now the SLA should be calculated as below


IF Pending Closed DT ( Column H) less than or equal to Registered DT (Column G) SLA MET (if not ) Pending Closed DT ( Column H) less than or equal to Restored DT (Column I) then SLA is met if not SLA NOT MET..

All Previous Priorities should be considered.
Waiting for your Reply