I don't know how to do this and keep it short. I have created an excel
workbook to do tournament events and the entire process is now automated
except for changing the start times of the events. I need a formula or custom
function that does 3 arguments. The one I created calculates the first two
arguments but not the third. I then tried to create a custom function but
that does not calculate the time either I get an error message. I have both
the formula and function for someone to review. Please help if you can.
Here is the formula and explination:

=If(and(SUM(M27,$AT$5,INDEX!$M$20)=(INDEX!M$21-$AT$5),iNDEX!$M$23,SUM(M27,$AT$5,INDEX!$M$20)) This would be if 10:00 =10:30,1:00,10:00
,+IF(SUM(M27,$AT$5,INDEX!$M$20)<=INDEX!$M$23,INDEX !$M$23,SUM(M27,$AT$5,INDEX!$M$20)) This would be if 10:00<=1:00,1:00,10:00
,+IF(SUM(M27,$AT$5,INDEX!$M$20)<=SUM(M3,$AT$5,INDE X!$M20),SUM(M27,$AT$5,INDEX!$M$20,INDEX!$M$22),SUM (M27,$AT$5,INDEX!$M$20)))
This would be if 10:00<=10:15,10:40,10:00

To sum up what I need to have the time calculation do is not enter any times
between for example 11:00 and 1:00 and then I need to make sure that the time
does not overlap a previous time. The third part of the formula makes sure
that there is at least 20 to 30 min from the end of the previous game time.

When this didn't work I tried creating a custom function. I assigned all the
dim statements as variants and then assigned names to each cell and
expression. However I received the following error message when running the
function: AN END IF STATEMENT MUST HAVE A CORRESPONDING IF STATEMENT.

Function Evtime(time1,time2)
Dim statements as variant

Gameint=Index!M20
Lunchbk=Index!M21
Addtm=Index!M22
Pmstart=Index!M23
Gametm=AT5
Exp1=(Time1+Gametm+Gameint)
Exp2=(Lunchbk-Gametm)
Exp3=(Time2+Gametm+Gameint)
Exp4=(Time1+Gametm+Gameint+Addtm)

Result 1 is the first half of the calculation

If Exp1<Exp2 then Result 1=Exp1 Else Result 1=Pmstart
If Exp1Exp2 Then Result 1=Exp1 Else Result 1=Pmstart
If Exp1= Exp3 then evtime=Result 1 Else Evtime=Exp4
End If
End Function

If anyone can help me with this problem and get me on the right track I
would be most greatful. As I said this is the only thing from stopping me
from having a completely automated scoring system.

Thanks and sorry to be so lengthy

Bill_De

If Exp1<Exp2...
If Exp1Exp2...
If Exp1= Exp3...
End If <=== get rid of this line
End Function

HTH
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Bill_De" wrote:

I don't know how to do this and keep it short. I have created an excel
workbook to do tournament events and the entire process is now automated
except for changing the start times of the events. I need a formula or custom
function that does 3 arguments. The one I created calculates the first two
arguments but not the third. I then tried to create a custom function but
that does not calculate the time either I get an error message. I have both
the formula and function for someone to review. Please help if you can.
Here is the formula and explination:

=If(and(SUM(M27,$AT$5,INDEX!$M$20)=(INDEX!M$21-$AT$5),iNDEX!$M$23,SUM(M27,$AT$5,INDEX!$M$20)) This would be if 10:00 =10:30,1:00,10:00
,+IF(SUM(M27,$AT$5,INDEX!$M$20)<=INDEX!$M$23,INDEX !$M$23,SUM(M27,$AT$5,INDEX!$M$20)) This would be if 10:00<=1:00,1:00,10:00
,+IF(SUM(M27,$AT$5,INDEX!$M$20)<=SUM(M3,$AT$5,INDE X!$M20),SUM(M27,$AT$5,INDEX!$M$20,INDEX!$M$22),SUM (M27,$AT$5,INDEX!$M$20)))
This would be if 10:00<=10:15,10:40,10:00

To sum up what I need to have the time calculation do is not enter any times
between for example 11:00 and 1:00 and then I need to make sure that the time
does not overlap a previous time. The third part of the formula makes sure
that there is at least 20 to 30 min from the end of the previous game time.

When this didn't work I tried creating a custom function. I assigned all the
dim statements as variants and then assigned names to each cell and
expression. However I received the following error message when running the
function: AN END IF STATEMENT MUST HAVE A CORRESPONDING IF STATEMENT.

Function Evtime(time1,time2)
Dim statements as variant

Gameint=Index!M20
Lunchbk=Index!M21
Addtm=Index!M22
Pmstart=Index!M23
Gametm=AT5
Exp1=(Time1+Gametm+Gameint)
Exp2=(Lunchbk-Gametm)
Exp3=(Time2+Gametm+Gameint)
Exp4=(Time1+Gametm+Gameint+Addtm)

Result 1 is the first half of the calculation

If Exp1<Exp2 then Result 1=Exp1 Else Result 1=Pmstart
If Exp1Exp2 Then Result 1=Exp1 Else Result 1=Pmstart
If Exp1= Exp3 then evtime=Result 1 Else Evtime=Exp4
End If
End Function

If anyone can help me with this problem and get me on the right track I
would be most greatful. As I said this is the only thing from stopping me
from having a completely automated scoring system.

Thanks and sorry to be so lengthy

Bill_De

Gary thanks for the help on this I no longer get the error message on the if
statement. However I am fairly new at Excel and Access doing formulas and I
have another problem which after an hour I am stuck and Help doesn't give
enough information for me to work through this.

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!

Here is the entire function with explinations on cell formate and entries I
hope you can give me some advice on this please as I am lost.

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 and thanks again
Bill_De

"Gary L Brown" wrote:

If Exp1<Exp2...
If Exp1Exp2...
If Exp1= Exp3...
End If <=== get rid of this line
End Function

HTH
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Bill_De" wrote:

I don't know how to do this and keep it short. I have created an excel
workbook to do tournament events and the entire process is now automated
except for changing the start times of the events. I need a formula or custom
function that does 3 arguments. The one I created calculates the first two
arguments but not the third. I then tried to create a custom function but
that does not calculate the time either I get an error message. I have both
the formula and function for someone to review. Please help if you can.
Here is the formula and explination:

=If(and(SUM(M27,$AT$5,INDEX!$M$20)=(INDEX!M$21-$AT$5),iNDEX!$M$23,SUM(M27,$AT$5,INDEX!$M$20)) This would be if 10:00 =10:30,1:00,10:00
,+IF(SUM(M27,$AT$5,INDEX!$M$20)<=INDEX!$M$23,INDEX !$M$23,SUM(M27,$AT$5,INDEX!$M$20)) This would be if 10:00<=1:00,1:00,10:00
,+IF(SUM(M27,$AT$5,INDEX!$M$20)<=SUM(M3,$AT$5,INDE X!$M20),SUM(M27,$AT$5,INDEX!$M$20,INDEX!$M$22),SUM (M27,$AT$5,INDEX!$M$20)))
This would be if 10:00<=10:15,10:40,10:00

To sum up what I need to have the time calculation do is not enter any times
between for example 11:00 and 1:00 and then I need to make sure that the time
does not overlap a previous time. The third part of the formula makes sure
that there is at least 20 to 30 min from the end of the previous game time.

When this didn't work I tried creating a custom function. I assigned all the
dim statements as variants and then assigned names to each cell and
expression. However I received the following error message when running the
function: AN END IF STATEMENT MUST HAVE A CORRESPONDING IF STATEMENT.

Function Evtime(time1,time2)
Dim statements as variant

Gameint=Index!M20
Lunchbk=Index!M21
Addtm=Index!M22
Pmstart=Index!M23
Gametm=AT5
Exp1=(Time1+Gametm+Gameint)
Exp2=(Lunchbk-Gametm)
Exp3=(Time2+Gametm+Gameint)
Exp4=(Time1+Gametm+Gameint+Addtm)

Result 1 is the first half of the calculation

If Exp1<Exp2 then Result 1=Exp1 Else Result 1=Pmstart
If Exp1Exp2 Then Result 1=Exp1 Else Result 1=Pmstart
If Exp1= Exp3 then evtime=Result 1 Else Evtime=Exp4
End If
End Function

If anyone can help me with this problem and get me on the right track I
would be most greatful. As I said this is the only thing from stopping me
from having a completely automated scoring system.

Thanks and sorry to be so lengthy

Bill_De