Time Extraction One More Try :)

[David M]

Thank you for all of your responses however I still am not
making it clear so I will try this approach..here are my
columns I need to work from:

C3(start date)D3(Start Time)E3(End Date)F3(End Time)

4/16/04 6:45AM 4/16/04 7:00AM

L3 contains the formula to figure total minutes
=(((E3+F3)-(C3+D3))*1440)

end date + end time - start date + start time *1440
returns 15 minutes


Okay what if the times were this? 1:00AM to 2:40AM
What I would need to do is.. as I enter data I need excel
to say okay wait... here is a time frame that falls within
my allowance 1AM - 2AM but there is a 40min overage...so
instead of figuring the total minutes as being 100minutes,
I want to take only the 40minutes or whatever the ovreage
is..lets say it was 10 minutes...I need to use that number
as actual minutes to be figured in the overall total
amount for downtime.

Can this be done? Can I alter or maybe use a condition or
VBA to flag whenerver I beging to enter 1Am to whatever...
and then do the math and extraction form there?

Right now I have to check over the work and fidge the
times..

Is this a better explanation??

Keep in mind, I want to be able to do this as data is
being entered...

Thanx again

D

 

[Dave R.]

Perhaps you are new to these newsgroups..

You'll find that keeping in the same (viz. original) thread will get the
question answered a lot quicker. I suggest posting this info there, as
people generally keep track of which threads they have replied to (and this
new one would NOT be one of them).

Do the people who replied a courtesy, and keep replies in the original
thread.

 

[David M]

Thank you..will do ..I have recieved replies but there it
seemed I was not clear in my request so I tried a
different approach...no offense meant to anyone who has
helped me..I wantged to give more information and you are
correct I am new to this group and have found it most
helpful...

Thank you

 

[Arvi Laanemets]

Hi

For start one with all dates in one day.
A1 - FrameStart
B1 - FrameEnd
A3 - StartTime
B3 - EndTime
the formula
=(IF(B3>B1,B3,B1)-IF(A3>B1,A3,B1))+(IF(B3<A1,B3,A1)-IF(A3<B1,A3,A1))
returns the time ("h:mm") falling outside the frame.

when you allow times over midnight, then you have to consider all
combinations of:
A1>B1
A1<B1
A3>B3
A3<B3
As result, the complexity of formel grows enormously, and probably it's
better when you spilit the calculation between time period before and after
the frame - maybe you can use named ranges for this.