sum of hours
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
-----Original Message-----
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(F3E3,(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
-----Original Message-----
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?
"Papou" wrote
in message
...
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" a écrit dans le
message de
...
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.
.
.
|