Forum Discussion
Solved
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 an...
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.