sum of hours

[Raymond]

I run a small business that I need to schedule the work hours for my
employees on a sheet. A particular cell contains the hours work for an
mployee from 2 am-5 Pm, 2 pm- 9pm. That were exactly what I put down.
However, I want excel to automatically add the total hours worked by each
employee in the right most colum. However, I cannot use the sum function
with the format from above. Is there a different way for me to input the
hours, so that the sum function will work, but I still want a format
similar to the once from above?

Thank You.

 

[Papou]

Hi Raymond
Have a look at Number formats, Hour Category you will find precisely the
number format you are looking for.
Please note you must input hours as 00:00:00 (AM or PM mention will be
automatically added)

HTH
Cordially
Pascal

 

[Raymond]

THANK YOU PAPOU. HOWEVER, WHAT IF I WANT TO HAVE A COUPLE OF DAYS OFF FOR
AN EMPLOYEE BY PUTTING THE WORD OFF THERE? IT WOULD NOT ADD UP TO A RIGHT
NUMBER, IS IT?

 

[John]

This is a little convoluded but here goes:

if cell a3 is '2am-5pm

=SEARCH("-",A3,1) is put in cell b3

=LEFT(A3,B3-1) is put in c3

=RIGHT(A3,LEN(A3)-B3) is put in c3

=IF(RIGHT(C3,2)="am",VALUE(LEFT(C3,LEN(C3)-2)&" am"),VALUE
(LEFT(C3,LEN(C3)-2)&" pm")) in e3 give you the start time

=IF(RIGHT(D3,2)="am",VALUE(LEFT(D3,LEN(D3)-2)&" am"),VALUE
(LEFT(D3,LEN(D3)-2)&" pm")) in f3 give you the end time

=IF(F3>E3,(F3-E3)*24,(F3+1-E3)*24) in g3 give you the
hours difference (assuming that start time and end time
are on same day or successive days)

John

 

[John]

I should add it would be far simpler to put the start time
in one column and end time in another (formatted to show
hh:mm) and then just calculate the difference....if you
want the option to put in "off" you could just start with
if (cell="off",0,normal calculation).

John