# Thread: vlookup finding minutes and accumulating the time to hours

1. ## 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.

2. 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. =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. 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. 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
•