Complicated Time Formula

[Jay]

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.

 

[Rob van Gelder]

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

 

[Guest]

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 !

 

[shockley]

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

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Kevin Beckham]

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(FinishTime>C3,60,IF(FinishTime<C2,0,
(FinishTime-C2)*24*60)),IF(StartTime>C3,0,(C3-StartTime)
*24*60))

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

Kevin Beckham

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Kevin Beckham]

Too much haste, corrected version

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

Kevin Beckham

 

[Norman Harker]

Hi Kevin!

Bin there, done that!

Seems to check out OK.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

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

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jay]

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

 

[Rob van Gelder]

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

 

[Jay]

That did it Thank you Rob

 

[Jay]

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

 

[Rob van Gelder]

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

 

[Rob van Gelder]

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

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Rob van Gelder]

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]

Hi Rob!

Re:
"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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
message news:[email protected]...