Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check time in cells to see if it goes across lunch
Hi all
Is there a way to check the time in two cells to make sure that it doesn't count a lunch break. ie Start time is 8:45 end time is 16:25 so I need to subtract 30 minutes, however if I run an if statement and say use 12:30 if the job starts after 12:30 it will still subtract 30 minutes which I don't want. Also If it ends before 12:00 again no time subtraction. Thanks all |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check time in cells to see if it goes across lunch
On Sat, 09 Feb 2008 10:52:10 -0600, koob <koob@koob wrote:
Hi all Is there a way to check the time in two cells to make sure that it doesn't count a lunch break. ie Start time is 8:45 end time is 16:25 so I need to subtract 30 minutes, however if I run an if statement and say use 12:30 if the job starts after 12:30 it will still subtract 30 minutes which I don't want. Also If it ends before 12:00 again no time subtraction. Thanks all =End-Start-TIME(,30,)*AND(Start<TIME(12,,),EndTIME(12,30,)) --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check time in cells to see if it goes across lunch
=B2-A2-TIME(0,30,0)*AND(A2<=TIME(12,30,0),B2=TIME(12,0,0 ))
-- David Biddulph "koob" <koob@koob wrote in message ... Hi all Is there a way to check the time in two cells to make sure that it doesn't count a lunch break. ie Start time is 8:45 end time is 16:25 so I need to subtract 30 minutes, however if I run an if statement and say use 12:30 if the job starts after 12:30 it will still subtract 30 minutes which I don't want. Also If it ends before 12:00 again no time subtraction. Thanks all |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check time in cells to see if it goes across lunch
Hi,
If you are looking for a general solution, as I see it there are 6 possibilities: 1. Start before 12, end after 12:30 2. Start before 12, end before 12 3. Start before 12, end during lunch (say 12:15) 4. Start after lunch, (end after lunch ) 5. Start during lunch, end after lunch 6. Start during lunch, end during lunch To cover all of these situations the formula is a little bit more complicated, suppose your Start time is in cell A1 and your End time in B1 then: =IF(AND(B1=TIME(12,30,),A1<=TIME(12,,)),B1-A1-TIME(,30,),IF(AND(A1=TIME(12,,),B1<=TIME(12,30,)) ,0,IF(AND(A1TIME(12,,),A1<TIME(12,30,)),B1-TIME(12,30,),IF(AND(A1<=TIME(12,,),B1TIME(12,,)), TIME(12,,)-A1,B1-A1)))) To simplify this formula: In cell F1 enter =TIME(12,,) In cell F2 enter =TIME(12,30,) Name cell F1 L and name cell F2 U Then you can use the revised version of the above formula: =IF(AND(B1=U,A1<=L),B1-A1-TIME(,30,),IF(AND(A1=L,B1<=U),0,IF(AND(A1L,A1<U) ,B1-U,IF(AND(A1<=L,B1L),L-A1,B1-A1)))) To further simplify the formula you can determine if any of the 6 conditions I listed would never apply and remove the appropriate portion of the formula. For example suppose they can never start or end during lunch, then the formula can be simplified to read: =B1-A1-AND(A1<0.5,B112.5/24)*.5/24 In this case I have not used range names but I have eliminated the need for the longer TIME() function. or with the range names =B1-A1-(AND(A1<L,B1U)*.5/24) -- Cheers, Shane Devenshire "koob" wrote: Hi all Is there a way to check the time in two cells to make sure that it doesn't count a lunch break. ie Start time is 8:45 end time is 16:25 so I need to subtract 30 minutes, however if I run an if statement and say use 12:30 if the job starts after 12:30 it will still subtract 30 minutes which I don't want. Also If it ends before 12:00 again no time subtraction. Thanks all |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check time in cells to see if it goes across lunch
How would you do this manually, on paper? When you determine that, you can
do it in Excel using the same formula. Tyro "koob" <koob@koob wrote in message ... Hi all Is there a way to check the time in two cells to make sure that it doesn't count a lunch break. ie Start time is 8:45 end time is 16:25 so I need to subtract 30 minutes, however if I run an if statement and say use 12:30 if the job starts after 12:30 it will still subtract 30 minutes which I don't want. Also If it ends before 12:00 again no time subtraction. Thanks all |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time and lunch breaks | Excel Worksheet Functions | |||
How to substract for lunch on =MOD function | Excel Worksheet Functions | |||
Time Calculation For A Timesheet To Include Lunch | Excel Worksheet Functions | |||
Assumed Lunch | Excel Discussion (Misc queries) | |||
How can Excel do daily timesheet, -lunch time, & not use colon in. | Excel Worksheet Functions |