Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom functions calculating time arguments Help Desperate
I am fairly new at Excel and Access doing formulas and I
have a problem which after an hour I am stuck and Help doesn't give enough information for me to work through this. I have created an Excel document for scoring tournements. The entire book is automated and I am now trying to automate the times for the events using a custom function to change the start times. I finished the custom function and inserted it into a cell I then got the function arguments box which asks for entries for Time1 and Time2. I enter 2 cell locations i.e. M3, M15 and when I click ok I get the following: #Value! I have checkd the entire worksheet where I have time entries and all of the cells are formated to €śtime€ť. I have also taken cells with time entries ie cell M3 is 1:00 PM, and Cell AT5 is 0:20 and when I add the two cells I get a result of 1:20 PM and when I subtract the cells I get a result of 12:50 PM. I cannot figure out why I am getting the #Value error message as I check with help and all of the entries are in the same formate. Here is the entire function with explinations on cell formate and entries I hope you can give me some advice as to what I need to change to get this function to work I am totally lost on this one. Function Evtime(Time1, Time2) (Times in cells ie M3,M15 cell formats are custom h:mn AM/PM Dim gameint as Variant Dim Lunchbk as Variant Dim Addtm as Variant Dim Gametm as Variant Dim Pmstart as Variant Dim Result1 as Variant Gameint=Index!M20 (0:05 cell format custom [h]:mm) Lunchbk=Index!M21 (11:00 AM cell format custom h:mm AM/PM) Addtm=Index!M22 (0:20 cell format custom [h]:mm) Pmstart=Index!M23 (1:00 PM cell format custom h:mm AM/PM) Gametm=AT5 (0:20 cell format custom [h]:mm) Exp1=(Time1+Gametm+Gameint) Exp2=(Lunchbk-Gametm) Exp3=(Time2+Gametm+Gameint) Exp4=(Time1+Gametm+Gameint+Addtm) If Exp1< Exp2 Then Result1=Exp1 Else: Result1= Pmstart If Exp1 Exp2 Then Result1=Pmstart Else: Result1=Exp1 If Exp1 Exp3 Then Evtime=Result1 Else: Evtime= Exp4 End Function I hope you can help me as I am getting close to where I will be needing this for some tournements. Thanks again Bill_De |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells with time format and calculating the diffrence | New Users to Excel | |||
Calculating time and pay in excel | Excel Discussion (Misc queries) | |||
Calculating time between successful data transmissions | Excel Discussion (Misc queries) | |||
Calculating effective time from start/end date+time | Excel Worksheet Functions | |||
calculating date time ranges | Excel Worksheet Functions |