Solved Convert hh:mm to decimal number in excel

June 5, 2013 at 10:55:12
Specs: Windows 7
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

See More: Convert hh:mm to decimal number in excel

Report •

June 5, 2013 at 11:55:35
✔ Best Answer
With your data like:

    A          B         C         D        E  
1) 8:00 AM   12:00 PM   1:00 PM   5:15 PM  8.25

In cell E1 enter the formula:


See how that works for you.


Report •

June 5, 2013 at 12:24:38
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.25

Note: You may run into issues if your times cross midnight.

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

Report •

Related Solutions

Ask Question