Payroll/Timesheet adding hours and minutes together

[mikeg450]

Hi,

I'm hoping someone can help with this issue because I just cannot seem to figure it out. This is for payroll so I routinely run over 23 hours on time sheets. The program we use to clock in and out gives me hours in one column and minutes in another. I can get those combined using a simple formula, but then it gives me a percentage of an hour which isn't very helpful. I have tried the time function but anything over 24 hours resets the clock so to speak. I have tried all the formatting suggestions I could find online but still no luck. I need to take hours in column A, minutes in column b, and add them together in a normal HH:MM format that regularly exceeds 24 hours. Any suggestions?

[#2 · Accepted solution]

@mikeg450 

Assuming A2 contains 30 (hours) and B2 contains 15 (minutes) then how about...

=TIMEVALUE(A2&":"&B2)+IF(A2>23,1,0)

and setting the format of the formula cell as [hh]:mm

The above formula will return 30:15.

[#3]

@Subodh_Tiwari_sktneerThat did it! Thanks a ton. Just out of curiosity, I'm not entirely sure that I understand the if statement. Why does adding that value if it's over 23 hours fix the issue?

[#4]

@mikeg450

You're welcome. Glad it worked as desired.

Excel stores time as decimal numbers and 1 is equivalent to 24 hours and the IF formula checks the hours and if it is greater than or equal to 24, it adds 1 day or 24 hours to the time obtained by subtracting the two times.