Solved

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

Posted on 2013-01-08
470 Views
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
Question by:James Murrell
• 4
• 3

LVL 26

Expert Comment

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

LVL 31

Author Comment

Thanks I like the idea of splitting it? how?

also should i add another column if SLA 2 ,SLA3
0

LVL 26

Expert Comment

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

LVL 31

Author Comment

0

LVL 26

Accepted Solution

redmondb earned 350 total points
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

LVL 92

Assisted Solution

Patrick Matthews earned 150 total points
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

LVL 31

Author Closing Comment

Thanks, what would we do without EE
0

LVL 26

Expert Comment

Thanks, cs97jjm3!
0

Featured Post

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!