A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Timesheet in and out rounding



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 07:15 PM posted to microsoft.public.excel.worksheet.functions
Paula
external usenet poster
 
Posts: 278
Default Timesheet in and out rounding

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
  #2  
Old June 2nd, 2010, 08:06 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Timesheet in and out rounding

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


  #3  
Old June 2nd, 2010, 08:06 PM posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
external usenet poster
 
Posts: 516
Default Timesheet in and out rounding

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


  #4  
Old June 2nd, 2010, 08:16 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Timesheet in and out rounding

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.