Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtract formula
i had this formula a few months ago of Bob Phillips, the purpose of the formula is to remove set times froma n overall time span it works fine unless the fault happens after 02:00:00 (in this case it counts the whole time period What i want it to do is ignore the times 02:00:00 to 02:30:00 =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) any ideas how i can make this happen thanks kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtract formula
It looks to me to disregard 2:00 to 2:30 Kevin, 9:30 to 4:30 returns 17:30,
19 hours mines 30 mins for 10-10:30, 30 mins for 18-18:30 and 30 mins for 2-2:30. Give an example where it errors and state what slots it should ignore. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... i had this formula a few months ago of Bob Phillips, the purpose of the formula is to remove set times froma n overall time span it works fine unless the fault happens after 02:00:00 (in this case it counts the whole time period What i want it to do is ignore the times 02:00:00 to 02:30:00 =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) any ideas how i can make this happen thanks kevin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtract formula
On 1 Apr, 12:58, "Bob Phillips" wrote:
It looks to me to disregard 2:00 to 2:30 Kevin, 9:30 to 4:30 returns 17:30, 19 hours mines 30 mins for 10-10:30, 30 mins for 18-18:30 and 30 mins for 2-2:30. Give an example where it errors and state what slots it should ignore. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kevcar40" wrote in message ... i had this formula a few months ago of Bob Phillips, the purpose of the formula is to remove set *times froma n overall time span it works fine unless *the fault happens after 02:00:00 (in this case it counts the whole time period What i want it to do is ignore the times *02:00:00 to *02:30:00 =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) any ideas how i can make this happen thanks kevin Hi Bob AS requested i have listed below some of the returns from the formaula As you can see if the problem starts after 02:00:00 or ends before 02:30:00 it is not ignoring the time range Start Time End Time Total Time 01:55:58 02:35:00 00:09:02 01:58:59 02:22:58 00:23:59 01:40:59 02:35:35 00:24:36 02:01:29 02:31:17 00:29:48 01:59:00 02:31:17 00:02:17 thanks for your Help Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I SET UP A FORMULA TO SUBTRACT AFTER EACH ROW I COMPLETE | Excel Worksheet Functions | |||
how do I write a formula if b2 is less than a2 then subtract | Excel Discussion (Misc queries) | |||
formula to subtract time, 12:10 am (00:10) - 11:50 pm (23:10) ? | New Users to Excel | |||
I would like to know how to enter the formula that would subtract. | Excel Worksheet Functions | |||
How do I create a formula that would allow me to subtract from a d | New Users to Excel |