# Thread: if function help needed

1. ## if function help needed

Hi ,
my excel B column contains -13:31 -13:12 -11:56 1:16 1:17 1:20 2:02 2:05 3:07 3:09 7:00
8:00 8:02
like wise nearly more than 100 are there
my result C column to have Neg Neg Neg 1-2 HR 1-2 HR 1-2 HR 2-3 HR 2-3 HR 3-4 HR 3-4 HR 7-8 HR >8 HR >8 HR
could you please provide me an IF formula or some formula so that i can paste formula in C column
B column is time in time in hrs:mins and C column is result which am typing manually.
can we automate C column.
Time(hh:mm) final Needed
-13:31 Neg
-13:12 Neg
-11:56 Neg
1:16 1-2 HR
1:17 1-2 HR
1:20 1-2 HR
2:02 2-3 HR
2:05 2-3 HR
3:07 3-4 HR
3:09 3-4 HR
7:00 7-8 HR
8:00 >8 HR
8:02 >8 HR
rameshnarne@gmail.com

2. rameshnarne,

Welcome to the Lounge as a NEW Poster!

If I understand your requirements correctly this formula should do the trick, at least it worked in my tests using your data.

=IF(IFERROR(HOUR(\$A2),0)=0,"NEG",IF(HOUR(\$A2)>=8," >8 HR",TEXT(HOUR(\$A2),"#")&"-"&TEXT(HOUR(\$A2)+1,"#")&" HR"))

HourGroups.JPG
Note: My formula is in Col D above but will work as written if you place it in Row 2 of any column then fill down.

HTH

3. Ramesh,

You might also try:

=IF(ISERROR(HOUR(A2)),"Neg",IF(HOUR(A2)>=8,">8 HR",HOUR(A2)&"-"&HOUR(A2)+1&" HR"))

in cell C2 and copy down.

negHours1.png

HTH
Maud

4. Here is another way using the Left and Len functions

=IF(VALUE(LEFT(A2,LEN(A2)-3))<0,"Neg",IF(VALUE(LEFT(A2,LEN(A2)-3))>=8,">8 HR",LEFT(A2,LEN(A2)-3)&"-"&VALUE(LEFT(A2,LEN(A2)-3))+1&" HR"))

Alexandra

5. You can never have too many solutions.
So here is another:
=IF(LEFT(A2,1)="-","Neg",VLOOKUP(HOUR(A2),block1,2))

This one uses a lookup range named block1, which I put on another sheet in the attached sample file.

The shortest formula you can use would probably be a custom function, like
=TAT(a2)
..and Maud is the goto guy for custom functions

zeddy

6. Hi

..so here's my version using a custom function =TAT(cell)

Same file, but smaller in size because..
=TAT(A2)
..is a lot shorter than
=IF(LEFT(A2,1)="-","Neg",VLOOKUP(HOUR(A2),block1,2))

(Also, eagle-eyed spotters may have noticed I missed the 0 - 1 HR case in my previous file)

zeddy

7. Just as an alternative UDF:

Code:
```Function TAT(cell As Range) As String
Dim zValue
Const clMAX_BAND As Long = 8

zValue = cell.Value

If Left\$(zValue, 1) = "-" Then
TAT = "Neg"
Else
zhour = Hour(zValue)
TAT = Replace(Replace(Partition(zhour, 0, clMAX_BAND - 1, 1), ": " & zhour, "-" & zhour + 1 & " HR"), _
clMAX_BAND & ":", ">" & clMAX_BAND)
End If
End Function```

8. ## The Following User Says Thank You to rory For This Useful Post:

zeddy (2015-03-09)

9. Hi Rory

So that makes 6 solutions so far, with you winning the prize for the shortest.
I replaced a partition in my house once.
Never tried that in Excel though.
Have to remember not to forget that one now.

zeddy

10. Just a side-thought Ramesh: it is a really bad idea to put your full email address on a public forum - there are naughty people who harvest email addresses and sell them . . . and the next thing is that your inbox is full of spam messages !

There are lots of ways to at least disguise your address, such as writing nameATgmail.com etc etc, but it is better to leave it out altogether.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•