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.

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


.

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.
.



.