Hi -- I have a basic timesheet  in Excel 2003  - that has In and out with 
in/out for meals - I want to know if there is away that I can round the times 
only of the In and Out for the day --
I need a 10 minute window -- example is they manually enter their in time in 
C7 and their out in C10 - (their meals are in C8 and C9 will be actual times, 
no need to round)that if the are coming in at 8am and they enter their time 
at 7:50am that it will round the time to 8am, and same with the out -- out at 
5pm and they enter 5:10 that it would round to 5pm -- Right now it is 
calculating actual times using this formula =SUM((C10-C9)*24,(C8-C7)*24) -- 
is there a way to get it to round the in and outs in the total?
Thanks so much--Paula

I'm a little confused.

Do you want the mealtime subtracted from the total time?

I will assume that's the case.

Start time in C7
Start meal in C8
End meal in C9
Quit time in C1

=3DROUND((C10-C7)*24,0)-((C9-C8)*24)

Returns 8,5 hours with 30 minutes for lunch.


Gord Dibben  MS Excel MVP


On Wed, 2 Jun 2010 11:15:11 -0700, Paula =
<Paula@discussions.microsoft.com>
wrote:

>Hi -- I have a basic timesheet  in Excel 2003  - that has In and out =
with=20
>in/out for meals - I want to know if there is away that I can round the =
times=20
>only of the In and Out for the day --
>I need a 10 minute window -- example is they manually enter their in =
time in=20
>C7 and their out in C10 - (their meals are in C8 and C9 will be actual =
times,=20
>no need to round)that if the are coming in at 8am and they enter their =
time=20
>at 7:50am that it will round the time to 8am, and same with the out -- =
out at=20
>5pm and they enter 5:10 that it would round to 5pm -- Right now it is=20
>calculating actual times using this formula =
=3DSUM((C10-C9)*24,(C8-C7)*24) --=20
>is there a way to get it to round the in and outs in the total?
>Thanks so much--Paula

Experiment with this
=(ROUNDDOWN(C10*96,0)/96-C9+ROUNDUP(C8*96,0)/96-C7)*24
Not there is no need to use SUM when doing a simple arithmetic operation
best wishes

-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
REMINDER: These newsgroups are about to die. We will all meet again at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel

"Paula" <Paula@discussions.microsoft.com> wrote in message 
news:7E0AC6B5-BD5A-4F27-8D01-92F5A769E096@microsoft.com...
> Hi -- I have a basic timesheet  in Excel 2003  - that has In and out with
> in/out for meals - I want to know if there is away that I can round the 
> times
> only of the In and Out for the day --
> I need a 10 minute window -- example is they manually enter their in time 
> in
> C7 and their out in C10 - (their meals are in C8 and C9 will be actual 
> times,
> no need to round)that if the are coming in at 8am and they enter their 
> time
> at 7:50am that it will round the time to 8am, and same with the out -- out 
> at
> 5pm and they enter 5:10 that it would round to 5pm -- Right now it is
> calculating actual times using this formula 
> =SUM((C10-C9)*24,(C8-C7)*24) -- 
> is there a way to get it to round the in and outs in the total?
> Thanks so much--Paula 

Quit time should read C10


Gord

On Wed, 02 Jun 2010 12:06:27 -0700, Gord Dibben <gorddibbATshawDOTca> =
wrote:

>I'm a little confused.
>
>Do you want the mealtime subtracted from the total time?
>
>I will assume that's the case.
>
>Start time in C7
>Start meal in C8
>End meal in C9
>Quit time in C1
>
>=3DROUND((C10-C7)*24,0)-((C9-C8)*24)
>
>Returns 8,5 hours with 30 minutes for lunch.
>
>
>Gord Dibben  MS Excel MVP
>
>
>On Wed, 2 Jun 2010 11:15:11 -0700, Paula =
<Paula@discussions.microsoft.com>
>wrote:
>
>>Hi -- I have a basic timesheet  in Excel 2003  - that has In and out =
with=20
>>in/out for meals - I want to know if there is away that I can round the=
 times=20
>>only of the In and Out for the day --
>>I need a 10 minute window -- example is they manually enter their in =
time in=20
>>C7 and their out in C10 - (their meals are in C8 and C9 will be actual =
times,=20
>>no need to round)that if the are coming in at 8am and they enter their =
time=20
>>at 7:50am that it will round the time to 8am, and same with the out -- =
out at=20
>>5pm and they enter 5:10 that it would round to 5pm -- Right now it is=20
>>calculating actual times using this formula =
=3DSUM((C10-C9)*24,(C8-C7)*24) --=20
>>is there a way to get it to round the in and outs in the total?
>>Thanks so much--Paula