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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|