Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time interval worksheet function
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!B27,"OVER",IF(MIN(Data!G2,Data!H2,Data!I2,Da ta!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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time interval worksheet function
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? "Zb Kornecki" wrote: 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!B27,"OVER",IF(MIN(Data!G2,Data!H2,Data!I2,Da ta!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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time interval worksheet function
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" wrote: 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!B27,"OVER",IF(MIN(Data!G2,Data!H2,Data!I2,Da ta!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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time interval worksheet function
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 "Joel" wrote: 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? "Zb Kornecki" wrote: 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!B27,"OVER",IF(MIN(Data!G2,Data!H2,Data!I2,Da ta!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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
request for time interval worksheet function | Excel Discussion (Misc queries) | |||
Excel to recalculate a worksheet based on a time interval | Excel Worksheet Functions | |||
Calculating time interval (some across midnight) | Excel Worksheet Functions | |||
Calculations with date time interval | Excel Worksheet Functions | |||
time interval calculations in excel | Excel Discussion (Misc queries) |