Hi,

TRY ....

Function Evtime(Time1, Time2)


Dim gameint As Variant
Dim Lunchbk As Variant
Dim Addtm As Variant
Dim Gametm As Variant
Dim Pmstart As Variant
Dim Result1 As Variant
With Worksheets("Index")
gameint = .Range("M20") '(0:05 cell format custom [h]:mm)
Lunchbk = .Range("M21") ' (11:00 AM cell format custom h:mm AM/PM)
Addtm = .Range("M22") '(0:20 cell format custom [h]:mm)
Pmstart = .Range("M23") '(1:00 PM cell format custom h:mm AM/PM)
Gametm = .Range("AT5") ' (0:20 cell format custom [h]:mm)
End With
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

"Bill_De" wrote:

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