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 •


#1
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:

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

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#2
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