Im using an excel spreadsheet with clock in and out times in hours and minutes (hh:mm). I want a formula and formate to add up the total hours worked each day and convert it to a decimal number of hours worked for payroll purposes.

ie.... clock

In 8:00am

Out 12:00pm

In 1:00pm

Out 5:15pm

The column total hours to read 8.25 hrs

All times entered are rounded BEFORE entry on the worksheet by the employee to the quarter hour....15,30,45, or 00

With your data like: A B C D E 1) 8:00 AM 12:00 PM 1:00 PM 5:15 PM 8.25In cell E1 enter the formula:

=HOUR((D1-A1)-(C1-B1))+MINUTE((D1-A1)-(C1-B1))/60

See how that works for you.

MIKE

Another option: Format Column C as Number, then use the formulas shown:

A B C 1 In 8:00 AM 2 Out 12:00 PM =(B2-B1)*24 -------> 4.00 3 In 1:00 PM 4 Out 5:15 PM =(B4-B3)*24 -------> 4.25 =SUM(C2,C4) -------> 8.25Note: You may run into issues if your times cross midnight.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History