Making Time Right

[John L]

I need your help. I am doing a spreadsheet for my daughter. She needs t
total up the hours of each of her servers(resturant).This is Monday t
Sunday. I have it set up that i can get the total hours for each perso
but when she has someone start on the 1/2 hour it goes to the full hour
Example : if someone has 5 1/2 hours it gives me 6. The function i use
is...=sum(e4,e10,e15,e20,e25,e30,e35).

This works but when any one of the cells is in 1/2 hours it goes to th
full hour. What other function can be used that will give me what eve
time is entered? 1/2,3/4 hour.
Sorry if this is not explained well. I am Really new at this. I find i
interesting.

John

 

[Guest]

John,
What is the format of the cells your are summing ..is it hh:mm
or decimal numbers i.e. 5.5 is 5 1/2 hours.

And what is the format of the cell containing the sum?

 

[Guest]

Hi,
You should set your cell format to 1 decimal point and key in 1/2 as 0.5 and
3/4 as 0.75 so you can solve your problem.

Regards,
Lim

 

[John L]

Thanks Toppers,
I did not expect a reply so soon. I need to be at my daughters compute
to answer any guestions. I will try and answer what i think you ar
asking,
In the sum cell, since i thought i would be using full hours it is i
full numbers.
What should i format the cell as? As i mentioned if she uses full hour
then it works fine. But she has people who start on the 1/2 hour. Mayb
my problem is the wrong format in the total hours cell.

Now your more confused,
John

 

[John L]

Thanks Lim,
That is what i thought. I will try that when i get to her conputer

 

[John L]

Here is where i am at this time.
I got it to do the .5 or.75 time thing.
But what now is my problem is figuring out employee time.
In cell ( A ) is start time. Amber is on row 3
In cell ( b ) is end time.
In cell (c ) is name.
In ( d ) is Hours.

5:00 PM - 11:00 PM - Amber- 6.0 This is ok

This is what I have...=(b3-a3)*24

Using the above formula.
If an employee starts at 5:00 pm and works till 11:00 pm it work
fine.
If an employee starts at 5:00 pm and works till 12:00 am it give
-17.0
If an employee starts at 5:00 pm and works till 1:00 am it gives -16.0

So what is my solution?

John

 

[Sandy Mann]

John,

For times, including those that cross midnight try using:

=((C3-B3)+(B3>C3))*24

or

=MOD(C3-B3,1)*24

with the cell formatted as General or Number

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[John L]

Thanks Sandy Mann,
This one seems to work.
=MOD(C3-B3,1)*24

Thanks Everone on this site.
I will be back. My friends think i am the greatest. You won't tell them
that i was here will ya. Thanks John