Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction formula
Hi
I have a worksheet containing time faults occur ie Start time End Time 06:23:12 07:23:12 08:10:23 12:10:23 08:25:10 04:25:10 what i am looking to do is subtract the start time from the end time and exclude break times ie break 1 10:00:00 to 10:30:00 break 2 18:00:00 to 18:30:00 break 3 02:00:00 to 02:30:00 End Time Start time 07:23:12 - 06:23:12 = 01:00:00 12:10:23 - 08:10:23 = 03:30:00 19:15:00 - 15:15:20 = 03:30:00 20:10:23 - 08:10:23 = 09:00:00 04:25:10 - 08:25:10 = 18:30:00 any ideas on a formula thanks kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction formula
This seems to work
=MOD(D20-C20,1)-((AND(C20<=TIME(2,0,0),(D20=TIME(2,30,0))))+(AND( C20<=TIME(10,0,0),(D20=TIME(10,30,0))))+(AND(C20< =TIME(18,0,0),(D20=TIME(18,30,0)))) +(AND(C20D20,D20=TIME(2,30,0))) )*TIME(0,30,0) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... Hi I have a worksheet containing time faults occur ie Start time End Time 06:23:12 07:23:12 08:10:23 12:10:23 08:25:10 04:25:10 what i am looking to do is subtract the start time from the end time and exclude break times ie break 1 10:00:00 to 10:30:00 break 2 18:00:00 to 18:30:00 break 3 02:00:00 to 02:30:00 End Time Start time 07:23:12 - 06:23:12 = 01:00:00 12:10:23 - 08:10:23 = 03:30:00 19:15:00 - 15:15:20 = 03:30:00 20:10:23 - 08:10:23 = 09:00:00 04:25:10 - 08:25:10 = 18:30:00 any ideas on a formula thanks kevin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction formula
On 31 Jan, 10:24, "Bob Phillips" wrote:
This seems to work =MOD(D20-C20,1)-((AND(C20<=TIME(2,0,0),(D20=TIME(2,30,0))))+(AND( C20<=TIME*(10,0,0),(D20=TIME(10,30,0))))+(AND(C20 <=TIME(18,0,0),(D20=TIME(18,30,0))*)) * * * * * * * * * * * * * *+(AND(C20D20,D20=TIME(2,30,0))) )*TIME(0,30,0) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... Hi I have a worksheet containing time faults occur ie * * Start time * * * End Time * * * 06:23:12 * * * *07:23:12 * * * 08:10:23 * * * *12:10:23 * * * 08:25:10 * * * *04:25:10 what i am looking to do is subtract the start time from the end time and exclude break times ie break 1 *10:00:00 *to 10:30:00 break 2 *18:00:00 to *18:30:00 break 3 * 02:00:00 to *02:30:00 * * End Time * * * * * Start time * * * 07:23:12 * *- * * *06:23:12 * * * * *= * *01:00:00 * * * 12:10:23 * *- * * *08:10:23 * * * * *= * *03:30:00 * * * 19:15:00 * *- * * *15:15:20 * * * * *= * *03:30:00 * * * 20:10:23 * *- * * *08:10:23 * * * * *= * *09:00:00 * * * 04:25:10 * *- * * *08:25:10 * * * * *= * *18:30:00 any ideas on a formula thanks kevin- Hide quoted text - - Show quoted text - Bob Thanks for Formula I have a slight ploblem with it the formula works fine unitl the end time goes past 00:00:00 then it returns wrong answer it is out by 1 hour it works fine when both start and end time are after 00:00:00 i have played with formula but getting nowhere any ideas? thanks kevin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction formula
Kevin,
Can you give me an example that errors. I tried it with your examples, including 08:25-04:25 and got the same results as you predicted. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... On 31 Jan, 10:24, "Bob Phillips" wrote: This seems to work =MOD(D20-C20,1)-((AND(C20<=TIME(2,0,0),(D20=TIME(2,30,0))))+(AND( C20<=TIME*(10,0,0),(D20=TIME(10,30,0))))+(AND(C20 <=TIME(18,0,0),(D20=TIME(18,30,0))*)) +(AND(C20D20,D20=TIME(2,30,0))) )*TIME(0,30,0) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... Hi I have a worksheet containing time faults occur ie Start time End Time 06:23:12 07:23:12 08:10:23 12:10:23 08:25:10 04:25:10 what i am looking to do is subtract the start time from the end time and exclude break times ie break 1 10:00:00 to 10:30:00 break 2 18:00:00 to 18:30:00 break 3 02:00:00 to 02:30:00 End Time Start time 07:23:12 - 06:23:12 = 01:00:00 12:10:23 - 08:10:23 = 03:30:00 19:15:00 - 15:15:20 = 03:30:00 20:10:23 - 08:10:23 = 09:00:00 04:25:10 - 08:25:10 = 18:30:00 any ideas on a formula thanks kevin- Hide quoted text - - Show quoted text - Bob Thanks for Formula I have a slight ploblem with it the formula works fine unitl the end time goes past 00:00:00 then it returns wrong answer it is out by 1 hour it works fine when both start and end time are after 00:00:00 i have played with formula but getting nowhere any ideas? thanks kevin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction formula
On 31 Jan, 14:13, "Bob Phillips" wrote:
Kevin, Can you give me an example that errors. I tried it with your examples, including 08:25-04:25 and got the same results as you predicted. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... On 31 Jan, 10:24, "Bob Phillips" wrote: This seems to work =MOD(D20-C20,1)-((AND(C20<=TIME(2,0,0),(D20=TIME(2,30,0))))+(AND( C20<=TIME**(10,0,0),(D20=TIME(10,30,0))))+(AND(C2 0<=TIME(18,0,0),(D20=TIME(18,30,0)*)*)) +(AND(C20D20,D20=TIME(2,30,0))) )*TIME(0,30,0) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... Hi I have a worksheet containing time faults occur ie Start time End Time 06:23:12 07:23:12 08:10:23 12:10:23 08:25:10 04:25:10 what i am looking to do is subtract the start time from the end time and exclude break times ie break 1 10:00:00 to 10:30:00 break 2 18:00:00 to 18:30:00 break 3 02:00:00 to 02:30:00 End Time Start time 07:23:12 - 06:23:12 = 01:00:00 12:10:23 - 08:10:23 = 03:30:00 19:15:00 - 15:15:20 = 03:30:00 20:10:23 - 08:10:23 = 09:00:00 04:25:10 - 08:25:10 = 18:30:00 any ideas on a formula thanks kevin- Hide quoted text - - Show quoted text - Bob Thanks for Formula I have a slight ploblem with it the formula works fine unitl the end time goes past 00:00:00 then it returns wrong answer it is out by 1 hour it works fine when both start and end time are after 00:00:00 i have played with formula but getting nowhere any ideas? thanks kevin- Hide quoted text - - Show quoted text - Bob I have tried this data Start time End time Result Expected Result 09:30:30 12:30:00 2:30:00 (3 - 30) 2:30:00 09:30:00 19:30:00 9:00:00 (10-1) 9:00:00 09:30:00 04:30:00 18:30:00 (19-1:30) 17:30:00 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction formula
On 31 Jan, 14:56, kevcar40 wrote:
On 31 Jan, 14:13, "Bob Phillips" wrote: Kevin, Can you give me an example that errors. I tried it with your examples, including 08:25-04:25 and got the same results as you predicted. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... On 31 Jan, 10:24, "Bob Phillips" wrote: This seems to work =MOD(D20-C20,1)-((AND(C20<=TIME(2,0,0),(D20=TIME(2,30,0))))+(AND( C20<=TIME***(10,0,0),(D20=TIME(10,30,0))))+(AND(C 20<=TIME(18,0,0),(D20=TIME(18,30,0*)*)*)) +(AND(C20D20,D20=TIME(2,30,0))) )*TIME(0,30,0) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message .... Hi I have a worksheet containing time faults occur ie Start time End Time 06:23:12 07:23:12 08:10:23 12:10:23 08:25:10 04:25:10 what i am looking to do is subtract the start time from the end time and exclude break times ie break 1 10:00:00 to 10:30:00 break 2 18:00:00 to 18:30:00 break 3 02:00:00 to 02:30:00 End Time Start time 07:23:12 - 06:23:12 = 01:00:00 12:10:23 - 08:10:23 = 03:30:00 19:15:00 - 15:15:20 = 03:30:00 20:10:23 - 08:10:23 = 09:00:00 04:25:10 - 08:25:10 = 18:30:00 any ideas on a formula thanks kevin- Hide quoted text - - Show quoted text - Bob Thanks for Formula I have a slight ploblem with it the formula works fine unitl the end time goes past 00:00:00 then it returns wrong answer it is out by 1 hour it works fine when both start and end time are after 00:00:00 i have played with formula but getting nowhere any ideas? thanks kevin- Hide quoted text - - Show quoted text - Bob I *have tried this data Start time ***End time Result * * * * ** * *Expected *Result 09:30:30 * * 12:30:00 * * 2:30:00 (3 - 30) * * 2:30:00 09:30:00 * * 19:30:00 * * 9:00:00 (10-1) * * * 9:00:00 09:30:00 * * *04:30:00 * 18:30:00 (19-1:30) *17:30:00 sorry text wrapped hope is better kevin |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction formula
This looks better Kevin
=MOD(D2-C2,1)-((AND(C2<=TIME(2,0,0),(D2=TIME(2,30,0))))+(AND(C2 <=TIME(10,0,0),(D2=TIME(10,30,0))))+(AND(C2<=TIME (18,0,0),(D2=TIME(18,30,0)))) +(AND(C2D2,C2<=TIME(10,0,0)))+(AND(C2D2,C2<=TIME (18,0,0)))+(AND(C2D2,D2=TIME(2,30,0))) )*TIME(0,30,0) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... On 31 Jan, 14:56, kevcar40 wrote: On 31 Jan, 14:13, "Bob Phillips" wrote: Kevin, Can you give me an example that errors. I tried it with your examples, including 08:25-04:25 and got the same results as you predicted. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... On 31 Jan, 10:24, "Bob Phillips" wrote: This seems to work =MOD(D20-C20,1)-((AND(C20<=TIME(2,0,0),(D20=TIME(2,30,0))))+(AND( C20<=TIME***(10,0,0),(D20=TIME(10,30,0))))+(AND(C 20<=TIME(18,0,0),(D20=TIME(18,30,0*)*)*)) +(AND(C20D20,D20=TIME(2,30,0))) )*TIME(0,30,0) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... Hi I have a worksheet containing time faults occur ie Start time End Time 06:23:12 07:23:12 08:10:23 12:10:23 08:25:10 04:25:10 what i am looking to do is subtract the start time from the end time and exclude break times ie break 1 10:00:00 to 10:30:00 break 2 18:00:00 to 18:30:00 break 3 02:00:00 to 02:30:00 End Time Start time 07:23:12 - 06:23:12 = 01:00:00 12:10:23 - 08:10:23 = 03:30:00 19:15:00 - 15:15:20 = 03:30:00 20:10:23 - 08:10:23 = 09:00:00 04:25:10 - 08:25:10 = 18:30:00 any ideas on a formula thanks kevin- Hide quoted text - - Show quoted text - Bob Thanks for Formula I have a slight ploblem with it the formula works fine unitl the end time goes past 00:00:00 then it returns wrong answer it is out by 1 hour it works fine when both start and end time are after 00:00:00 i have played with formula but getting nowhere any ideas? thanks kevin- Hide quoted text - - Show quoted text - Bob I have tried this data Start time End time Result Expected Result 09:30:30 12:30:00 2:30:00 (3 - 30) 2:30:00 09:30:00 19:30:00 9:00:00 (10-1) 9:00:00 09:30:00 04:30:00 18:30:00 (19-1:30) 17:30:00 sorry text wrapped hope is better kevin |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction formula
On 31 Jan, 19:00, "Bob Phillips" wrote:
This looks better Kevin =MOD(D2-C2,1)-((AND(C2<=TIME(2,0,0),(D2=TIME(2,30,0))))+(AND(C2 <=TIME(10,0*,0),(D2=TIME(10,30,0))))+(AND(C2<=TIM E(18,0,0),(D2=TIME(18,30,0)))) * * * * * * * * * * * * +(AND(C2D2,C2<=TIME(10,0,0)))+(AND(C2D2,C2<=TIME (18,0,0)))+(AND(C2D2,D2*=TIME(2,30,0))) *)*TIME(0,30,0) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... On 31 Jan, 14:56, kevcar40 wrote: On 31 Jan, 14:13, "Bob Phillips" wrote: Kevin, Can you give me an example that errors. I tried it with your examples, including 08:25-04:25 and got the same results as you predicted. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message .... On 31 Jan, 10:24, "Bob Phillips" wrote: This seems to work =MOD(D20-C20,1)-((AND(C20<=TIME(2,0,0),(D20=TIME(2,30,0))))+(AND( C20<=TIME****(10,0,0),(D20=TIME(10,30,0))))+(AND( C20<=TIME(18,0,0),(D20=TIME(18,30,*0*)*)*)) +(AND(C20D20,D20=TIME(2,30,0))) )*TIME(0,30,0) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... Hi I have a worksheet containing time faults occur ie Start time End Time 06:23:12 07:23:12 08:10:23 12:10:23 08:25:10 04:25:10 what i am looking to do is subtract the start time from the end time and exclude break times ie break 1 10:00:00 to 10:30:00 break 2 18:00:00 to 18:30:00 break 3 02:00:00 to 02:30:00 End Time Start time 07:23:12 - 06:23:12 = 01:00:00 12:10:23 - 08:10:23 = 03:30:00 19:15:00 - 15:15:20 = 03:30:00 20:10:23 - 08:10:23 = 09:00:00 04:25:10 - 08:25:10 = 18:30:00 any ideas on a formula thanks kevin- Hide quoted text - - Show quoted text - Bob Thanks for Formula I have a slight ploblem with it the formula works fine unitl the end time goes past 00:00:00 then it returns wrong answer it is out by 1 hour it works fine when both start and end time are after 00:00:00 i have played with formula but getting nowhere any ideas? thanks kevin- Hide quoted text - - Show quoted text - Bob I have tried this data Start time End time * * Result Expected Result 09:30:30 12:30:00 * 2:30:00 (3 - 30) 2:30:00 09:30:00 19:30:00 * 9:00:00 (10-1) 9:00:00 09:30:00 04:30:00 * 18:30:00 (19-1:30) 17:30:00 sorry text wrapped hope is better kevin- Hide quoted text - - Show quoted text - Thank you Bob for all your help Works a treat thanks again kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I CREATE A SUBTRACTION FORMULA | Excel Worksheet Functions | |||
subtraction formula | Excel Worksheet Functions | |||
subtraction formula | Excel Worksheet Functions | |||
Subtraction formula | Excel Discussion (Misc queries) | |||
how do i do a subtraction formula | Excel Worksheet Functions |