CODEWITHSUNDEEP
excelexcel-formulaformula
Brett Nelson
Brett Nelson

Reputation: 113

Excel Formula overly complex

I have a formula that builds a string with If statements, CONCATENATE, and text formulas. My problem is that the formula's getting extremely larger than I ever wanted it.

My formula first looks to see if a cell if blank

=IF(I3="","",Else)

The Second part is to check if the letters "DKB" is in the H cell

=IF(ISNUMBER(SEARCH("*DKB*",$H3)),True,False)

The Third is if the duration (Cell F) has 0 hrs do not include (HH)

=IF(TEXT(F3,"HH")<>"00",CONCATENATE(TEXT(F3,"hh\h\r mm\m\i\n"),CONCATENATE(TEXT(F3,"mm\m\i\n"))

The Fourth one is if the are 0 min don't include min selection

=IF(TEXT(F3,"MM")<>"00",CONCATENATE(Text(F3,"HH:MM")),CONCATENATE(Text(F3,"HH"))

If I were to write all the ways this could get played out I would have a total of 10 IF's. I want a simple way to write for each option without having to write out each answer. I have a partial code but doesn't include the minute portion. Is there a better way to do this? as you can see me using only if statements I'm not an expert.

Here's a picture to demonstrate my sample data and sample output enter image description here

If we could have a variable for the first part ie:10-17 to 9:10 PM

2nd variable for duration

3rd variable for DKB

Would this be possible

Upvotes: 0

Views: 83

Answers (2)

Brett Nelson
Brett Nelson

Reputation: 113

=IF(I3="","",TEXT(A3,"mm-dd-yyyy")&" On-Site "&TEXT(E3,"hh:mm AM/PM")&"- "&TEXT(G3,"hh:mm AM/PM")&" "&IF(COUNTIF(F3,"*:00:*"),TEXT(F3,"hh\h\r"),IF(COUNTIF(F3,"00:*:*"),TEXT(F3,"mm\m\i\n"),TEXT(F3,"hh\h\r mm\m\i\n"))&IF(COUNTIF(H3,"*DKB*"),MID(H3,4,20),""))))

This Will Make is so It won't show 00hr or 00min

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

Perhaps something like this is what you're looking for:

=IF(I3="","",TEXT(A3,"mm-dd-yyyy")&" On-Site "&TEXT(E3,"hh:mm AM/PM")& "- "&TEXT(G3,"hh:mm AM/PM")&" "&TEXT(F3,"hh\h\r mm\m\i\n")&IF(COUNTIF(H3,"*DKB*"),MID(H3,4,20),""))

Upvotes: 1

Related Questions