Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    vlookup finding minutes and accumulating the time to hours

    I am trying to do a VLOOKUP pulling in minutes but it comes over as a decimal. I would then like to add all the minutes and have a total yielding hours and minutes.

    I am attaching a sample workbook. Worksheet "time w-s sorted" has the minutes in column L and Worksheet "Reimbursement" has the Vlookup in column J.

    Much appreciated.
    Attached Files Attached Files

  2. #2
    Pecoflyer
    Guest
    Copy of TIME MANIPULATION.xlsHi
    please find a solution in the attached file

    1.The decimal value you get after the vlookup is normal, it is time as XL sees it. Format to h:mm and you will see the usual time presentation
    2, I indicated the sum on the second sheet. Be sure that it is custom formatted as [h]:mm, otherwise your sum will not exceed 24 hrs
    3. In a formula like
    Code:
    =SUM(E5*0.5)
    the SUM function is redundant
    Code:
    =E5*0.5
    is perfect
    4. Same thing for the formula on the first sheet
    Code:
    =SUM(K2-J2)
    , the sum function is again redundant
    5.If your stop time gets after midnight, a formula like
    Code:
    =K2-J2
    will yield negative time, which is not accepted by XL. You van replace this formula with
    Code:
    =MOD(K2-J2,1)
    which will always work

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =MOD(K2-J2,1)
    It won't give a negative number, but it always assume that the 2 dates are just 1 day apart. If the times explicitly include dates with the times, this formula wipes out any day difference. so a difference of 2 hrs, 1 day and 2 hours, 2 days and 2 hours, etc will only indicate a 2 hour difference for all of them. This may not always be appropriate

    Ignoring the negative values may also be masking a symptom of a mistyped number which would become more difficult to find

    I would keep the simpler formula and include date information with the time if date changes are occuring or explicitly account for it in the formula

    Steve

  4. #4
    Pecoflyer
    Guest
    As the OP's example does not mention any dates,the MOD approach will do in my opinion. But then again, one can make things as complicated as one sees fit.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The OP's example doesn't include dates, but you are presuming date changes (if there are no date changes K2-J2 is perfectly acceptable). Your formula then presumes with these date changes that ALL difference are less than24 hours and any differences less than 0 are exactly 1 day (and 1 day only) later. No changes to the date values will correct these built-in presumptions. I point them out, since I think it is important for anyone following the thread to understand the presumptions in the calculations in case they want to use them. As I also indicated the formula will mask some mistyped numbers since you won't get error with negative numbers anymore as the formula just presume they are the next day.

    Steve

Posting Permissions

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