Time interval worksheet function

[Zb Kornecki]

I would appriciate some help writing a function, to measure several interval
along a timeline. however not every interval applies to every record. what
I envision is a function that takes 7 arguments

a. start time (there may be 1 to 5 times)
b. a way to indicate wether I want the min or max time
c. a high and low out range value
d,e & f. End time same 3 critera as start time
g. format choice for [h]:mm, just minutes or h.00

I currently use

=IF(MIN(Data!G2,Data!H2,Data!I2,Data!P2)-Data!B2>7,"OVER",IF(MIN(Data!G2,Data!H2,Data!I2,Data!P2)-Data!B2<0,"",MIN(Data!G2,Data!H2,Data!I2,Data!P2)-Data!B2))

but this doesn't really do all I need it to and is very time consuming. I do
to this to log times for an Emergency Dept's visits so it needs to be done on
a regular basis and the looked at intervals change regularly so I just can't
use a template. Any and all help would be truely appriciated
Thanks
Zb

 

[Joel]

I have two comments about your request

1) Do you want the return value to be in Microsoft time format or a string.
A function cannot control the format. if you have the actual time returned,
then you must format the cell to get your preferred formatt. You can have
the function return a string instead of time in two differentt formats.

2) You can't have a function with two diffferent parameters havving multiple
lengths. Can you specify a range of cells for the start and end times?

 

[Joel]

This is a general format for what you need. It will not work properly for
every combination of min and max. Modify it as you see fit.

Function TimeRange(StartTime As Range, MinMax As String, _
Limit As Single, Endtime As Range)
'a. start time (there may be 1 to 5 times)
'b. a way to indicate wether I want the min or max time
'c. a high and low out range value
'd, End time same (there may be 1 to 3 times)


If StrComp(StrConv(MinMax, vbUpperCase), "MAX") = 0 Then
First = True
For Each MyTime In StartTime
If First = True Then
MyStartTime = MyTime.Value
First = False
Else
If MyTime.Value > MyStartTime Then _
MyStartTime = MyTime.Value
End If
Next MyTime
First = True
For Each MyTime In Endtime
If First = True Then
MyEndTime = MyTime.Value
First = False
Else
If MyTime.Value > MyEndTime Then _
MyEndValue = MyTime.Value
End If
Next MyTime
Else
If StrComp(StrConv(MinMax, vbUpperCase), "MIN") = 0 Then
First = True
For Each MyTime In StartTime
If First = True Then
MyStartTime = MyTime.Value
First = False
Else
If MyTime.Value < MyStartTime Then _
MyValue = MyStartTime.Value
End If
Next MyTime
First = True
For Each MyTime In Endtime
If First = True Then
MyEndTime = MyTime.Value
First = False
Else
If MyTime.Value < MyEndTime Then _
MyEndTime = MyTime.Value
End If
Next MyTime
Else
TimeRange = "Error"
Exit Function
End If
End If
If (MyEndTime - MyStartTime) > Limit Then
TimeRange = "Over"
Else
TimeRange = MyEndTime - MyStartTime
End If
End Function

 

[Zb Kornecki]

Format is not important it would be a bonus but I can apply a format entire
colums.
as for a range of cells hte short answer probablly but the imputs are always
changing they may want the 1st of arrival or registration or eval and what
would be a range for on cell would be different in another. I appreaciate
your help and will play the code you sent me in the follow post
Zb

I have two comments about your request

1) Do you want the return value to be in Microsoft time format or a string.
A function cannot control the format. if you have the actual time returned,
then you must format the cell to get your preferred formatt. You can have
the function return a string instead of time in two differentt formats.

2) You can't have a function with two diffferent parameters havving multiple
lengths. Can you specify a range of cells for the start and end times?

I would appriciate some help writing a function, to measure several interval
along a timeline. however not every interval applies to every record. what
I envision is a function that takes 7 arguments

a. start time (there may be 1 to 5 times)
b. a way to indicate wether I want the min or max time
c. a high and low out range value
d,e & f. End time same 3 critera as start time
g. format choice for [h]:mm, just minutes or h.00

I currently use

=IF(MIN(Data!G2,Data!H2,Data!I2,Data!P2)-Data!B2>7,"OVER",IF(MIN(Data!G2,Data!H2,Data!I2,Data!P2)-Data!B2<0,"",MIN(Data!G2,Data!H2,Data!I2,Data!P2)-Data!B2))

but this doesn't really do all I need it to and is very time consuming. I do
to this to log times for an Emergency Dept's visits so it needs to be done on
a regular basis and the looked at intervals change regularly so I just can't
use a template. Any and all help would be truely appriciated
Thanks
Zb