Forum Discussion

Copper Contributor

Feb 11, 2020

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

excel

Formulas and Functions

Subodh_Tiwari_sktneer
Feb 11, 2020
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.

Subodh_Tiwari_sktneer

Silver Contributor

Feb 11, 2020

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.

mikeg450

Copper Contributor

Feb 11, 2020

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?

Subodh_Tiwari_sktneer
Silver Contributor
Feb 11, 2020
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.