formula to work out if if cell m2 < 3 days 4 hours and g2 = "SLA 1" then "SLA 1 met"

hi long day and need help with this in excel: ps i not a great excel user: BAsic i would say

I need the formula to work out if
if cell m2 < 3 days 4 hours  and g2 = "SLA 1" then "SLA 1 met"

example data
G2            m2
SLA 1      7 Days 3 Hours
SLA 1      3 Days 3 Hours
SLA 2      72 Days 23 Hours
SLA 1      4 Days 0 Hours
SLA 1      1 Days 0 Hours
LVL 31
James MurrellProduct SpecialistAsked:
Who is Participating?
 
redmondbCommented:
Thanks, cs97jjm3.

Please see attached. If you have lots of SLA's then they should probably be in a normal table rather than array constants.

Regards,
Brian.SLA-s.xlsx
0
 
redmondbCommented:
Hi, cs97jjm3.

Please see below for a single cell solution. However, it would be easier to maintain of it was split into multiple cells. Let me know if you want to do this.
=IF(G2<>"SLA 1","",IF(76>TRIM(MID(M2,1,FIND("Days",M2,1)-1))*24+TRIM(MID(M2,FIND("Days",M2,1)+4,FIND("Hours",M2,1)-FIND("Days",M2,1)-4))*1,"SLA 1 Met","SLA 1 Not Met"))

Regards,
Brian.
0
 
James MurrellProduct SpecialistAuthor Commented:
Thanks I like the idea of splitting it? how?

also should i add another column if SLA 2 ,SLA3
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
redmondbCommented:
cs97jjm3,

also should i add another column if SLA 2 ,SLA3
I thought that might be coming!

Which version of Excel are you using?

Thanks,
Brian.
0
 
James MurrellProduct SpecialistAuthor Commented:
2010   with all updates
0
 
Patrick MatthewsCommented:
Assuming you have a stable format of "# days # hours", then select that column, and use Data / Data Tools / Text to Columns in the Ribbon to split.

If you do that, so that now M has the number of days and O has the number of hours, it becomes much, much easier:

=IF(M2+O2/24<3+4/24,"SLA 1 Met","SLA 1 not met")
0
 
James MurrellProduct SpecialistAuthor Commented:
Thanks, what would we do without EE
0
 
redmondbCommented:
Thanks, cs97jjm3!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.