I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain
a start time and an end time. I need to calculate the
total time between the two times and place the value into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.

Try this user defined function:

A2 = 09:15
B2 = 11:30
C1 = 09:00
D1 = 10:00
E1 = 11:00
F1 = 12:00
C2 = =PeriodExposure($A2,$B2,C$1,D$1)
D2 = =PeriodExposure($A2,$B2,D$1,E$1)
E2 = =PeriodExposure($A2,$B2,E$1,F$1)

Function PeriodExposure(dStart As Date, dEnd As Date, dPeriodStart As Date,
dPeriodEnd As Date) As Date
Dim dtmTemp As Date

If dStart < dPeriodStart And dEnd < dPeriodStart Or _
dStart = dPeriodEnd And dEnd = dPeriodEnd Then
dtmTemp = 0
ElseIf dStart = dPeriodStart And dEnd < dPeriodEnd Then
dtmTemp = dEnd - dStart
ElseIf dStart < dPeriodStart And dEnd < dPeriodEnd Then
dtmTemp = dEnd - dPeriodStart
ElseIf dStart = dPeriodStart And dEnd = dPeriodEnd Then
dtmTemp = dPeriodEnd - dStart
Else
dtmTemp = dPeriodEnd - dPeriodStart
End If
PeriodExposure = dtmTemp
End Function


"Jay" wrote in message
...
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain
a start time and an end time. I need to calculate the
total time between the two times and place the value into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.

Read up on the DateDiff() and DateAdd() and DatePart() functions. These will help you extract the needed information. If you need more info, feel free to drop me an email. I am now just finishing the same thing for another client. Fun code !

Jay, Here's my offering:

Put a column of 24 cells somewhere on the worksheet with the values 0 to 23
and name it "Intervals" (I think range names are case sensitive, so be sure
your case matches the one in the macro).

Name the 2-cell range with the two input cells "TimeInput".

Name the 24-cell range adjacent (to the right) to the "Intervals" range
"Results".

Run this macro for each set of inputs:

HTW,
Shockley

Private arr(1 To 24, 2) As Integer
Sub Tester()
Range("Results").ClearContents
Erase arr
BeginTime = Range("TimeInput").Cells(1)
EndTime = Range("TimeInput").Cells(2)
HourSpan = Hour(EndTime) - Hour(BeginTime)
If HourSpan 0 Then
arr(1, 1) = Hour(BeginTime)
arr(1, 2) = 60 - Minute(BeginTime)
arr(2, 1) = Hour(EndTime)
arr(2, 2) = Minute(EndTime)
Else
arr(1, 1) = Hour(BeginTime)
arr(1, 2) = Minute(EndTime) - Minute(BeginTime)
End If
x = 2
If HourSpan 1 Then
For i = Hour(BeginTime) + 1 To Hour(EndTime) - 1
x = x + 1
arr(x, 1) = i
arr(x, 2) = 60
Next i
End If
EnterData
End Sub
Sub EnterData()
For i = 1 To 24
If arr(i, 1) = Empty Then Exit Sub
Range("Intervals").Find( _
What:=arr(i, 1), _
LookIn:=xlValues) _
.Offset(0, 1) _
= arr(i, 2)
Next i
End Sub



"Jay" wrote in message
...
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain
a start time and an end time. I need to calculate the
total time between the two times and place the value into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.

Hi Jay!

Here's a formula approach that looks OK:

A2: Start time
B2: Stop time
C1: 8:00
D1: =C1+1/24
Copied across

C2:
=IF(OR(HOUR(D1)<HOUR($A$2),HOUR($B$2)<HOUR(D1),$B$ 2=D1),0,IF(HOUR(D1)=
HOUR($A$2),MIN((E1-$A$2),($B$2-$A$2))*24*60,IF(HOUR(D1)=HOUR($B$2),($B
$2-D1)*24*60,60)))

I've defaulted times outside the range to 0. You may prefer ""
It seems to test OK but I'm not so sure that there aren't more
efficient approaches.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Jay" wrote in message
...
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain
a start time and an end time. I need to calculate the
total time between the two times and place the value into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.

To use the formula below, you will need to do a couple of
things first
1. insert two new rows, the first will contain the start
time for an interval,
the second will contain the finish time for the interval
e.g. below 9-10, the first cell will contain 09:00
the next cell will contain 10:00
2. name the start time cell as StartTime and the finish
time as FinishTime

now in the cells below these interval starts and finished,
add the following formula
(assuming this formula will go in a cell in the C column,
and that row 2 contains the interval start times,
and row 3 contains the interval finish times

=IF(StartTime<C2,IF(FinishTimeC3,60,IF(FinishTime <C2,0,
(FinishTime-C2)*24*60)),IF(StartTimeC3,0,(C3-StartTime)
*24*60))

The 24*60 converts the numbers from fractions of a day
into minutes

Kevin Beckham

-----Original Message-----
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain
a start time and an end time. I need to calculate the
total time between the two times and place the value into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.
.

Hi Kevin!

I think you may get an error with (e.g) start time = 9:00 finish time
= 9:45 (i.e. start and finish in the same hour slot.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Kevin Beckham" wrote in message
...
To use the formula below, you will need to do a couple of
things first
1. insert two new rows, the first will contain the start
time for an interval,
the second will contain the finish time for the interval
e.g. below 9-10, the first cell will contain 09:00
the next cell will contain 10:00
2. name the start time cell as StartTime and the finish
time as FinishTime

now in the cells below these interval starts and finished,
add the following formula
(assuming this formula will go in a cell in the C column,
and that row 2 contains the interval start times,
and row 3 contains the interval finish times

=IF(StartTime<C2,IF(FinishTimeC3,60,IF(FinishTime <C2,0,
(FinishTime-C2)*24*60)),IF(StartTimeC3,0,(C3-StartTime)
*24*60))

The 24*60 converts the numbers from fractions of a day
into minutes

Kevin Beckham

-----Original Message-----
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain
a start time and an end time. I need to calculate the
total time between the two times and place the value into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.
.

Too much haste, corrected version

=IF(StartTime<D2,IF(FinishTimeD3,60,IF(FinishTime <D2,0,
(FinishTime-D2)*24*60)),IF(StartTimeD3,0,IF(FinishTimeD3,
(D3-StartTime)*24*60,(FinishTime-StartTime)*24*60)))

Kevin Beckham

-----Original Message-----
Hi Kevin!

I think you may get an error with (e.g) start time = 9:00
finish time
= 9:45 (i.e. start and finish in the same hour slot.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Kevin Beckham"
wrote in message
...
To use the formula below, you will need to do a couple
of
things first
1. insert two new rows, the first will contain the start
time for an interval,
the second will contain the finish time for the
interval
e.g. below 9-10, the first cell will contain 09:00
the next cell will contain 10:00
2. name the start time cell as StartTime and the finish
time as FinishTime

now in the cells below these interval starts and
finished,
add the following formula
(assuming this formula will go in a cell in the C
column,
and that row 2 contains the interval start times,
and row 3 contains the interval finish times

=IF(StartTime<C2,IF(FinishTimeC3,60,IF(FinishTime <C2,0,
(FinishTime-C2)*24*60)),IF(StartTimeC3,0,(C3-StartTime)
*24*60))

The 24*60 converts the numbers from fractions of a day
into minutes

Kevin Beckham

-----Original Message-----
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that
contain
a start time and an end time. I need to calculate the
total time between the two times and place the value
into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would
be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.
.



.

Hi Kevin!

Bin there, done that!

Seems to check out OK.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Kevin Beckham" wrote in message
...
Too much haste, corrected version

=IF(StartTime<D2,IF(FinishTimeD3,60,IF(FinishTime <D2,0,
(FinishTime-D2)*24*60)),IF(StartTimeD3,0,IF(FinishTimeD3,
(D3-StartTime)*24*60,(FinishTime-StartTime)*24*60)))

Kevin Beckham

-----Original Message-----
Hi Kevin!

I think you may get an error with (e.g) start time = 9:00
finish time
= 9:45 (i.e. start and finish in the same hour slot.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Kevin Beckham"
wrote in message
...
To use the formula below, you will need to do a couple
of
things first
1. insert two new rows, the first will contain the start
time for an interval,
the second will contain the finish time for the
interval
e.g. below 9-10, the first cell will contain 09:00
the next cell will contain 10:00
2. name the start time cell as StartTime and the finish
time as FinishTime

now in the cells below these interval starts and
finished,
add the following formula
(assuming this formula will go in a cell in the C
column,
and that row 2 contains the interval start times,
and row 3 contains the interval finish times

=IF(StartTime<C2,IF(FinishTimeC3,60,IF(FinishTime <C2,0,
(FinishTime-C2)*24*60)),IF(StartTimeC3,0,(C3-StartTime)
*24*60))

The 24*60 converts the numbers from fractions of a day
into minutes

Kevin Beckham

-----Original Message-----
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that
contain
a start time and an end time. I need to calculate the
total time between the two times and place the value
into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would
be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.
.



.

Norman,

Thank you very much. This was the type of solution I was
hoping for, as I am not very finiliar with VBA. The
formula you suggested functions well, however it results
are placed into the wrong cell. They are off by one hour.
I.E.
A2 Start time 12:15
B2 End Time 12:30

In the 11:00 Column 15 comes up.

Thank you Very Much
-----Original Message-----
Hi Jay!

Here's a formula approach that looks OK:

A2: Start time
B2: Stop time
C1: 8:00
D1: =C1+1/24
Copied across

C2:
=IF(OR(HOUR(D1)<HOUR($A$2),HOUR($B$2)<HOUR
(D1),$B$2=D1),0,IF(HOUR(D1)=
HOUR($A$2),MIN((E1-$A$2),($B$2-$A$2))*24*60,IF(HOUR(D1)
=HOUR($B$2),($B
$2-D1)*24*60,60)))

I've defaulted times outside the range to 0. You may
prefer ""
It seems to test OK but I'm not so sure that there
aren't more
efficient approaches.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Jay" wrote in message
...
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that
contain
a start time and an end time. I need to calculate the
total time between the two times and place the value
into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would
be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.


.

Hi Jay!

We are obviously set up a little different because mine is coming up
OK.

A2 Start Time
B2 End Time
C1 8:00
D1 =C1+1/24
Copied across

Formula is in C2 copied across.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
wrote in message
...
Norman,

Thank you very much. This was the type of solution I was
hoping for, as I am not very finiliar with VBA. The
formula you suggested functions well, however it results
are placed into the wrong cell. They are off by one hour.
I.E.
A2 Start time 12:15
B2 End Time 12:30

In the 11:00 Column 15 comes up.

Thank you Very Much

I have it set up exactly as you describe and it still
comes up one hour off. Any Idea on how to have it
function with time period that pass through midnight.
Thank You again
Jay
-----Original Message-----
Hi Jay!

We are obviously set up a little different because mine
is coming up
OK.

A2 Start Time
B2 End Time
C1 8:00
D1 =C1+1/24
Copied across

Formula is in C2 copied across.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
wrote in message
...
Norman,

Thank you very much. This was the type of solution I
was
hoping for, as I am not very finiliar with VBA. The
formula you suggested functions well, however it
results
are placed into the wrong cell. They are off by one
hour.
I.E.
A2 Start time 12:15
B2 End Time 12:30

In the 11:00 Column 15 comes up.

Thank you Very Much


.

I think you'll find the formula works if you copy the formula to D2, not C2
as he suggested.

"Jay" wrote in message
...
I have it set up exactly as you describe and it still
comes up one hour off. Any Idea on how to have it
function with time period that pass through midnight.
Thank You again
Jay
-----Original Message-----
Hi Jay!

We are obviously set up a little different because mine
is coming up
OK.

A2 Start Time
B2 End Time
C1 8:00
D1 =C1+1/24
Copied across

Formula is in C2 copied across.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
wrote in message
...
Norman,

Thank you very much. This was the type of solution I
was
hoping for, as I am not very finiliar with VBA. The
formula you suggested functions well, however it
results
are placed into the wrong cell. They are off by one
hour.
I.E.
A2 Start time 12:15
B2 End Time 12:30

In the 11:00 Column 15 comes up.

Thank you Very Much


.

That did it Thank you Rob

One Problem down, one to go. The formula has trouble with
anytime around midnight. I get a negative number. Any
suggestions.

Thanks for your help
Jay

Could you supply the start and end times which are causing problems?


"Jay" wrote in message
...
One Problem down, one to go. The formula has trouble with
anytime around midnight. I get a negative number. Any
suggestions.

Thanks for your help
Jay

After having a very big think about your problem, it's much more complicated
than I first thought.

You have a start and end checking time range, call them cS, cE
You also have a start and end event time range, call them eS, eE

Consider also that an event may start before midnight and end after
midnight, but you are not dealing with dates. So it would appear the event
finished before it ended. eg. 23:30 to 02:30.

I've detected the following combinations in which the times may be ordered,
along with the outcome:
eS, eE, cS, cE = 0
eE, eS, cS, cE = cE-cS
eS, cS, eE, cE = eE-cS
eE, cS, eS, cE = cE-eS
eS, cS, cE, eE = cE-cS
eE, cS, cE, eS = 0
cS, eS, eE, cE = eE-eS
cS, eE, eS, cE = eE-cS + cE-eS
cS, eS, cE, eE = cE-eS
cS, eE, cE, eS = eE-cS
cS, cE, eS, eE = 0
cS, cE, eE, eS = cE-cS

So your formula for the first line could look like: if eS < eE and eE < cS
and cS < cE then 0, else.....
There's also opportunity to simplify since some of the outcomes are the same
(eg. = 0)

Hope this helps,

Rob



"Jay" wrote in message
...
One Problem down, one to go. The formula has trouble with
anytime around midnight. I get a negative number. Any
suggestions.

Thanks for your help
Jay

Hi Rob!

I've been playing with this for some time and am coming to the same
conclusions as you.

In my view, the data might need re-arranging so that each day is dealt
with separately so as to avoid the problems of spanning midnight. It's
not that it can't be done. But the solution is very complicated.

May I ask as an aside what is the underlying purpose of the splitting
up of the times? It may be that a more simple approach can achieve the
same results.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Rob van Gelder" wrote in
message ...
After having a very big think about your problem, it's much more
complicated
than I first thought.

You have a start and end checking time range, call them cS, cE
You also have a start and end event time range, call them eS, eE

Consider also that an event may start before midnight and end after
midnight, but you are not dealing with dates. So it would appear the
event
finished before it ended. eg. 23:30 to 02:30.

I've detected the following combinations in which the times may be
ordered,
along with the outcome:
eS, eE, cS, cE = 0
eE, eS, cS, cE = cE-cS
eS, cS, eE, cE = eE-cS
eE, cS, eS, cE = cE-eS
eS, cS, cE, eE = cE-cS
eE, cS, cE, eS = 0
cS, eS, eE, cE = eE-eS
cS, eE, eS, cE = eE-cS + cE-eS
cS, eS, cE, eE = cE-eS
cS, eE, cE, eS = eE-cS
cS, cE, eS, eE = 0
cS, cE, eE, eS = cE-cS

So your formula for the first line could look like: if eS < eE and
eE < cS
and cS < cE then 0, else.....
There's also opportunity to simplify since some of the outcomes are
the same
(eg. = 0)

Hope this helps,

Rob



"Jay" wrote in message
...
One Problem down, one to go. The formula has trouble with
anytime around midnight. I get a negative number. Any
suggestions.

Thanks for your help
Jay

I'm not sure what you mean by splitting up of the times.

I'm taking the checking range (eg. 9:00 to 10:00, or 10:00 to 11:00, etc..) and the event range (eg. 9:15 to 11:30)
The goal is to determine how much event time crosses the checking time range.
An assumption I've made is that the event will never be equal to or greater than 24 hours.

There are certainly optimisations to be made as the formula is implemented. I don't know how I could simplify the approach though.

On my scrap piece of paper, I drew two gantt chart looking lines. The top line for the event range, the bottom line for the checking range.
I'll try to draw it in ascii. You may need to switch to courier font (or a fixed-width font). These examples are for items 1, 3, 5 and 6 in the list I posted before.


1. =0
eS eE
|-------|
|---------------|
cS cE

3. =eE-cS
eS eE
|-------|
|---------------|
cS cE

5. =cE-cS
eS eE
|-----------------------|
|---------------|
cS cE

6. =0
eE eS
------| |------
|---------------|
cS cE


"Norman Harker" wrote in message ...
Hi Rob!

I've been playing with this for some time and am coming to the same
conclusions as you.

In my view, the data might need re-arranging so that each day is dealt
with separately so as to avoid the problems of spanning midnight. It's
not that it can't be done. But the solution is very complicated.

May I ask as an aside what is the underlying purpose of the splitting
up of the times? It may be that a more simple approach can achieve the
same results.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Rob van Gelder" wrote in
message ...
After having a very big think about your problem, it's much more
complicated
than I first thought.

You have a start and end checking time range, call them cS, cE
You also have a start and end event time range, call them eS, eE

Consider also that an event may start before midnight and end after
midnight, but you are not dealing with dates. So it would appear the
event
finished before it ended. eg. 23:30 to 02:30.

I've detected the following combinations in which the times may be
ordered,
along with the outcome:
eS, eE, cS, cE = 0
eE, eS, cS, cE = cE-cS
eS, cS, eE, cE = eE-cS
eE, cS, eS, cE = cE-eS
eS, cS, cE, eE = cE-cS
eE, cS, cE, eS = 0
cS, eS, eE, cE = eE-eS
cS, eE, eS, cE = eE-cS + cE-eS
cS, eS, cE, eE = cE-eS
cS, eE, cE, eS = eE-cS
cS, cE, eS, eE = 0
cS, cE, eE, eS = cE-cS

So your formula for the first line could look like: if eS < eE and
eE < cS
and cS < cE then 0, else.....
There's also opportunity to simplify since some of the outcomes are
the same
(eg. = 0)

Hope this helps,

Rob



"Jay" wrote in message
...
One Problem down, one to go. The formula has trouble with
anytime around midnight. I get a negative number. Any
suggestions.

Thanks for your help
Jay

Hi Rob!


"I'm not sure what you mean by splitting up of the times."

I was thinking in terms of breaking the times at Midnight

We might have a row of times from 00:00 to 23:00

Then break into another row from 00:00 to 23:00

The second row can be conditional on overlapping midnight. That way
the formula approach would not need to be adjusted for the case of
stop time < start time.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Rob van Gelder" wrote in
message ...

Wow thank you for all the help.
What my utilmate goal is to determine a utilization ratio
for an ambulance service I.e. if an ambulance was on a
call from 23:15 to 00:15, I want to be able to show that
that ambulance was active for 75% of the 23:00 time block
and 25% of the 00:00 time block. This easy to do when
dealing with one call, but I am dealing with several
thousand over a month long period. Once I am able to
determine the amount of time spent on each call in each
time block the rest is very easy to complete. Thank you
again. You guys have been great. The main hang up is the
00:00 time block.

Jay

Rob,
Okay. that is quite a few differant cominations. Would it
be at all helpfull if I was able to include the dates in
the start and end times? I do have that data available and
can incorperate it if need be. The question is I do not
want to incorperate the dates into the results, as I only
need a total time in each time block for my end result. Jay

Oh my word yes!!!!!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Jay" wrote in message
...
Rob,
Okay. that is quite a few differant cominations. Would it
be at all helpfull if I was able to include the dates in
the start and end times? I do have that data available and
can incorperate it if need be. The question is I do not
want to incorperate the dates into the results, as I only
need a total time in each time block for my end result. Jay

Suddenly I wish I had said I could do that earlier. Okay
so I can do one of two things. I can write a simple
formula to combine the two date and times into one or I
can use four cells.
i.e.
A2=start date
B2=start time
C2=end date
d2=end time
I did not mention this earlier because I didn't really
feel it would be needed. Sorry.
Thank You so much for the time and effort.

Hi JAY!

I'll take a look when my toothache goes away enough! But it looks
pretty straightforward now thanks to ability to have date added to the
time.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"JAY" wrote in message
...
Suddenly I wish I had said I could do that earlier. Okay
so I can do one of two things. I can write a simple
formula to combine the two date and times into one or I
can use four cells.
i.e.
A2=start date
B2=start time
C2=end date
d2=end time
I did not mention this earlier because I didn't really
feel it would be needed. Sorry.
Thank You so much for the time and effort.

Hi Norm,
If you were in the states I'd drive you to the dentist.
Thank you! Jay

Hi Jay!

Send a copy of the workbook you have to me and I'll play about with
it. Should be faster that way.

Rinsing mouth with Single Malt seems to be quite good for tooth ache.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Jay" wrote in message
...
Hi Norm,
If you were in the states I'd drive you to the dentist.
Thank you! Jay

Hi Jay!

I think that I've got it working OK now.

A1: Start Time
B1: Finish Time
A2: Named StartTime
A2 Sample entry: 30-Dec-2003 8:30
B2: Named FinishTime
B2 Sample entry: 30-Dec-2003 12:30
C1:
=DATE(YEAR(StartTime),MONTH(StartTime),DAY(StartTi me))
D1:
=C1+1/24
Copied across to AZ1 (i.e. covers 2 days + 1 hour)
C2:
=IF(StartTime<C1,IF(FinishTimeD1,60,IF(FinishTime <C1,0,(FinishTime-C1
)*24*60)),IF(StartTimeD1,0,IF(FinishTimeD1,(D1-StartTime)*24*60,(Fin
ishTime-StartTime)*24*60)))
Copied across to AY2 (note that the row 1 time overlaps the row 2
calculation by 1 hour (this prevents a problem with the final cell
calculation)

I've tested for the difficult problems of starting and finishing
within the hour and with over-lapping midnight and / or noon and it
seems OK

I'll send workbook if you want but you can construct from the above.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Jay" wrote in message
...
Hi Norm,
If you were in the states I'd drive you to the dentist.
Thank you! Jay

Norm,
I am glad you have found the cure for toothaches. Could
you send me the workbook. I must be doing something wrong
as I am getting errors in every cell from K2 on. My email
address is listed below. Also is this going to work if i
copy it down to perform this function for several hundred
sets if times?

Thank you
Jay

Hi Jay!

Have done!

I think it was the Single Malt that cured the toothache.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Jay" wrote in message
...
Norm,
I am glad you have found the cure for toothaches. Could
you send me the workbook. I must be doing something wrong
as I am getting errors in every cell from K2 on. My email
address is listed below. Also is this going to work if i
copy it down to perform this function for several hundred
sets if times?

Thank you
Jay

Hi Jay!

There will be some amendments need to facilitate copying down. But
nothing too serious. Let's get it working once first <vbg

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Jay" wrote in message
...
Norm,
I am glad you have found the cure for toothaches. Could
you send me the workbook. I must be doing something wrong
as I am getting errors in every cell from K2 on. My email
address is listed below. Also is this going to work if i
copy it down to perform this function for several hundred
sets if times?

Thank you
Jay

No words can describe how greatfull I am. Thank You. Now I
just need to get it working for several hundred rows. The
catch is there is going to be a months worth of dates to
work off of. I am going to end up summing each column for
each time slot. So they would need to be constant. Which
causes a problem with the dates in the first row.
Jay

here i send my offer without macros. Sorry for my english.
HEP


Jay wrote:
*I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain
a start time and an end time. I need to calculate the
total time between the two times and place the value into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.

Attachment filename: problema complejo de horarios.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=39367
--
Message posted from http://www.ExcelForum.com

Hi Hector!

We got it all working OK off newsgroup in the end.

The key formula was:

=IF(C1="","",IF($A2<C1,IF($B2D1,60,IF($B2<C1,0,($ B2-C1)*24*60)),IF($A
2D1,0,IF($B2D1,(D1-$A2)*24*60,($B2-$A2)*24*60))))

A2 contained the start date and time and B2 contained the stop date
and time.

We managed a copy down OK and a summing of the results.

The file of 10000 records is a bit big but I've suggested using
monthly files and then a summary file.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"HÉCTOR E. POLLA" wrote in
message ...
here i send my offer without macros. Sorry for my english.
HEP


Jay wrote:
*I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain
a start time and an end time. I need to calculate the
total time between the two times and place the value into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you. *

Attachment filename: problema complejo de horarios.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=393678
---
Message posted from http://www.ExcelForum.com/