Forum Discussion
Payroll/Timesheet adding hours and minutes together
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?
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 Replies
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.
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?
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.
