Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Feb 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question if function help needed

    Hi ,
    could you please help in solving my problem
    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
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Last edited by RetiredGeek; 2015-02-23 at 15:17.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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. #4
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    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. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Regards,
    Rory

    Microsoft MVP - Excel

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

    zeddy (2015-03-09)

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    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
  •