Making Time Right

[John L]

I need your help. I am doing a spreadsheet for my daughter. She needs to total up the hours of each of her servers(resturant).This is Monday to Sunday. I have it set up that i can get the total hours for each person 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 used 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 the full hour. What other function can be used that will give me what ever time is entered? 1/2,3/4 hour.
Sorry if this is not explained well. I am Really new at this. I find it interesting.

John L

[Toppers]

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?



"John L" wrote:

>
> I need your help. I am doing a spreadsheet for my daughter. She needs to
> total up the hours of each of her servers(resturant).This is Monday to
> Sunday. I have it set up that i can get the total hours for each person
> 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 used
> 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 the
> full hour. What other function can be used that will give me what ever
> time is entered? 1/2,3/4 hour.
> Sorry if this is not explained well. I am Really new at this. I find it
> interesting.
>
> John L
>
>
> --
> John L
> ------------------------------------------------------------------------
> John L's Profile: http://www.excelforum.com/member.php...o&userid=34774
> View this thread: http://www.excelforum.com/showthread...hreadid=571777
>
>

[Lim]

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" wrote:

>
> I need your help. I am doing a spreadsheet for my daughter. She needs to
> total up the hours of each of her servers(resturant).This is Monday to
> Sunday. I have it set up that i can get the total hours for each person
> 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 used
> 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 the
> full hour. What other function can be used that will give me what ever
> time is entered? 1/2,3/4 hour.
> Sorry if this is not explained well. I am Really new at this. I find it
> interesting.
>
> John L
>
>
> --
> John L
> ------------------------------------------------------------------------
> John L's Profile: http://www.excelforum.com/member.php...o&userid=34774
> View this thread: http://www.excelforum.com/showthread...hreadid=571777
>
>

[John L]

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

Now your more confused,
John L

[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 works fine.
If an employee starts at 5:00 pm and works till 12:00 am it gives -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 L

[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

[email protected]
[email protected] with @tiscali.co.uk


"John L" <[email protected]> wrote in
message news:[email protected]...
>
> 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 works
> fine.
> If an employee starts at 5:00 pm and works till 12:00 am it gives
> -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 L
>
>
> --
> John L
> ------------------------------------------------------------------------
> John L's Profile:
> http://www.excelforum.com/member.php...o&userid=34774
> View this thread: http://www.excelforum.com/showthread...hreadid=571777
>

[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