sum of hours
 

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.

sum of hours
 

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.

sum of hours
 

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.

sum of hours
 

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.






.

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.






.

.