Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

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
0
James Murrell
Asked:
James Murrell
  • 4
  • 3
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
James MurrellProduct SpecialistAuthor Commented:
2010   with all updates
0
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now