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

=ROUND((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
wrote:

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

=ROUND((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
wrote:

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

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/...ry/officeexcel

"Paula" wrote in message
...
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