Custom functions calculating time arguments Help Desperate

[Bill_De]

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

 

[Toppers]

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

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

 

[Bill_De]

Hi Toppers
I rewrote the function just as you wrote it and I still get the #Value!
error message. When I clicked on the trace error the blue line went to the
two cells in my main worksheet (M3,M11) with the message "A value in the
formula is of the wrong data type". I checked the format for the two cells
(M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
Worksheet DE2 M3= worng data type and M11= wrong data type even though both
are set for time.
I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
(0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
Gameint) the result on enter was 8:05 AM.

I checked the possible causes in help and I can't figure out what I am doing
wrong based on what help showed. Here are the possible causes:
-Entering text when the formula requires a number or a logical value, such
as true or false.
-entering or editing an array formula and then pressing enter.
-Entering a cell reference, a formula, or a function as an array constant.
-Supplying a range to an operator or a function that requires a single
value, not a range.
-Using a matrix that is not valid in one of the matrix worksheet functions.
-Running a macro that enters a function that returns #value!

Any suggestions on which way to go next. I just don't know which one of the
possible problems it could be. By the way sorry to be such a bother,
hopefully you can help me solve this problem and I will be out of your hair.

Thanks again for all the help
Bill_De

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

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

 

[Niek Otten]

Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't help. You
can test with the ISTEXT() function. Right? You probably copied the times from another application.
Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of characters
and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160) character
with "" (nothing).
Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).

--
Kind regards,

Niek Otten

| Hi Toppers
| I rewrote the function just as you wrote it and I still get the #Value!
| error message. When I clicked on the trace error the blue line went to the
| two cells in my main worksheet (M3,M11) with the message "A value in the
| formula is of the wrong data type". I checked the format for the two cells
| (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
| Worksheet DE2 M3= worng data type and M11= wrong data type even though both
| are set for time.
| I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
| (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
| Gameint) the result on enter was 8:05 AM.
|
| I checked the possible causes in help and I can't figure out what I am doing
| wrong based on what help showed. Here are the possible causes:
| -Entering text when the formula requires a number or a logical value, such
| as true or false.
| -entering or editing an array formula and then pressing enter.
| -Entering a cell reference, a formula, or a function as an array constant.
| -Supplying a range to an operator or a function that requires a single
| value, not a range.
| -Using a matrix that is not valid in one of the matrix worksheet functions.
| -Running a macro that enters a function that returns #value!
|
| Any suggestions on which way to go next. I just don't know which one of the
| possible problems it could be. By the way sorry to be such a bother,
| hopefully you can help me solve this problem and I will be out of your hair.
|
| Thanks again for all the help
| Bill_De
|
| "Toppers" wrote:
|
| > 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
| > >

 

[Toppers]

Further to Niek's note:

I tested the routine with data in the cells you identified and it worked OK.
The cells were formatted as you indicated but I obviously typed in data so it
was going to be in the right internal format. Niek has suggested you do this
to verify the logic.

(Niek, thanks for the feedback).

Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't help. You
can test with the ISTEXT() function. Right? You probably copied the times from another application.
Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of characters
and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160) character
with "" (nothing).
Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).

--
Kind regards,

Niek Otten

| Hi Toppers
| I rewrote the function just as you wrote it and I still get the #Value!
| error message. When I clicked on the trace error the blue line went to the
| two cells in my main worksheet (M3,M11) with the message "A value in the
| formula is of the wrong data type". I checked the format for the two cells
| (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
| Worksheet DE2 M3= worng data type and M11= wrong data type even though both
| are set for time.
| I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
| (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
| Gameint) the result on enter was 8:05 AM.
|
| I checked the possible causes in help and I can't figure out what I am doing
| wrong based on what help showed. Here are the possible causes:
| -Entering text when the formula requires a number or a logical value, such
| as true or false.
| -entering or editing an array formula and then pressing enter.
| -Entering a cell reference, a formula, or a function as an array constant.
| -Supplying a range to an operator or a function that requires a single
| value, not a range.
| -Using a matrix that is not valid in one of the matrix worksheet functions.
| -Running a macro that enters a function that returns #value!
|
| Any suggestions on which way to go next. I just don't know which one of the
| possible problems it could be. By the way sorry to be such a bother,
| hopefully you can help me solve this problem and I will be out of your hair.
|
| Thanks again for all the help
| Bill_De
|
| "Toppers" wrote:
|
| > 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
| > >

 

[Bill_De]

Thanks for the quick response. However I am very new at this Excel stuff and
am just learning about functions and writing code and all that stuff and I
have to say you have lost me some what.
I would like to reply to your response;
First I set the format when I created the document so the formating wasn't
done afterward it was done before I created the custom function. As far as
the times go they are not copied from another application the are
transferred/copied from another worksheet in the same workbook. I have a page
titled Index where I can enter all of the begining times and intervals and
then those time are sent to the first time slot of each game page.
Second there is a space between the numbers and the AM/PM but this is done
in the formating not by me. If I type in 7:30AM when I tab to another cell
the entry changes to 7:30 AM. Also if I overwrite the formating and enter
7:30AM then all of the other entries on the work sheet change to #Value!, and
when I change the entry back to 7:30 AM the formula works correct.
Next you lost me with the ISTEXT() function. Where do I insert the function
and what arguments do I put in the ().
You say that I should check to see that there are no spaces in the cell or
non-printable characters. Each cell has a formula in it for example the first
cell that I mentioned M3 when I click on the cell the formula is: =INDEX!F4
and what is viewed is 7:30 AM. I don't know if this is what you are talking
about when you say non-printable characters.

When I run the LEN function on cell M3 I get a result of "6" for the entry
7:30 AM. The result is 0.3125. When I run the LEN function on Cell M11 I get
a result of "7" for the entry 8:15 AM. The result is 0.34375.

I got the numbers 0.3125 and 0.34375 when I ran the TRIM() function. I
really am not sure what you are talking about with the CHAR(160) and replace
with"". Where do I enter this function when I entered it into Cell M3 the
result was: =INDEX!F4+CHAR(160).

Also when I used the ISTEXT(), TRIM() functions I ran them in empty cells I
don't know if I was suppose to run them in the cell where the problem is
M3,M11.

If you could help clarify this for me that would be great. Toppers said that
he ran this function and he had no problem and I don't know how he did that .

Please help
Thanks
Bill_De

Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't help. You
can test with the ISTEXT() function. Right? You probably copied the times from another application.
Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of characters
and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160) character
with "" (nothing).
Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).

--
Kind regards,

Niek Otten

| Hi Toppers
| I rewrote the function just as you wrote it and I still get the #Value!
| error message. When I clicked on the trace error the blue line went to the
| two cells in my main worksheet (M3,M11) with the message "A value in the
| formula is of the wrong data type". I checked the format for the two cells
| (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
| Worksheet DE2 M3= worng data type and M11= wrong data type even though both
| are set for time.
| I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
| (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
| Gameint) the result on enter was 8:05 AM.
|
| I checked the possible causes in help and I can't figure out what I am doing
| wrong based on what help showed. Here are the possible causes:
| -Entering text when the formula requires a number or a logical value, such
| as true or false.
| -entering or editing an array formula and then pressing enter.
| -Entering a cell reference, a formula, or a function as an array constant.
| -Supplying a range to an operator or a function that requires a single
| value, not a range.
| -Using a matrix that is not valid in one of the matrix worksheet functions.
| -Running a macro that enters a function that returns #value!
|
| Any suggestions on which way to go next. I just don't know which one of the
| possible problems it could be. By the way sorry to be such a bother,
| hopefully you can help me solve this problem and I will be out of your hair.
|
| Thanks again for all the help
| Bill_De
|
| "Toppers" wrote:
|
| > 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
| > >

 

[Bill_De]

Toppers,
Could you please check the response I sent to Niek's. I tried the best I
could to follow his instructions and still get the #VALUE! error. I see that
you ran the function and it worked fine. I just don't know what I am doing
wrong.
Please help
Thanks
Bill

Further to Niek's note:

I tested the routine with data in the cells you identified and it worked OK.
The cells were formatted as you indicated but I obviously typed in data so it
was going to be in the right internal format. Niek has suggested you do this
to verify the logic.

(Niek, thanks for the feedback).

Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't help. You
can test with the ISTEXT() function. Right? You probably copied the times from another application.
Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of characters
and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160) character
with "" (nothing).
Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).

--
Kind regards,

Niek Otten

| Hi Toppers
| I rewrote the function just as you wrote it and I still get the #Value!
| error message. When I clicked on the trace error the blue line went to the
| two cells in my main worksheet (M3,M11) with the message "A value in the
| formula is of the wrong data type". I checked the format for the two cells
| (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
| Worksheet DE2 M3= worng data type and M11= wrong data type even though both
| are set for time.
| I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
| (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
| Gameint) the result on enter was 8:05 AM.
|
| I checked the possible causes in help and I can't figure out what I am doing
| wrong based on what help showed. Here are the possible causes:
| -Entering text when the formula requires a number or a logical value, such
| as true or false.
| -entering or editing an array formula and then pressing enter.
| -Entering a cell reference, a formula, or a function as an array constant.
| -Supplying a range to an operator or a function that requires a single
| value, not a range.
| -Using a matrix that is not valid in one of the matrix worksheet functions.
| -Running a macro that enters a function that returns #value!
|
| Any suggestions on which way to go next. I just don't know which one of the
| possible problems it could be. By the way sorry to be such a bother,
| hopefully you can help me solve this problem and I will be out of your hair.
|
| Thanks again for all the help
| Bill_De
|
| "Toppers" wrote:
|
| > 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
| > >

 

[Niek Otten]

I tested Topper's code too, and it give results, not an error.

1. Did you copy and paste Topper's code, not just retype it? A typo can be easily overseen
2. Enter these formulas in empty cells on the INDEX sheet:
=ISTEXT(M20)
=ISTEXT(M21)
=ISTEXT(M22)
=ISTEXT(M23)
=ISTEXT(AT5)

They should all give FALSE as a result.

I'm sure we will get this right!
--
Kind regards,

Niek Otten

| Thanks for the quick response. However I am very new at this Excel stuff and
| am just learning about functions and writing code and all that stuff and I
| have to say you have lost me some what.
| I would like to reply to your response;
| First I set the format when I created the document so the formating wasn't
| done afterward it was done before I created the custom function. As far as
| the times go they are not copied from another application the are
| transferred/copied from another worksheet in the same workbook. I have a page
| titled Index where I can enter all of the begining times and intervals and
| then those time are sent to the first time slot of each game page.
| Second there is a space between the numbers and the AM/PM but this is done
| in the formating not by me. If I type in 7:30AM when I tab to another cell
| the entry changes to 7:30 AM. Also if I overwrite the formating and enter
| 7:30AM then all of the other entries on the work sheet change to #Value!, and
| when I change the entry back to 7:30 AM the formula works correct.
| Next you lost me with the ISTEXT() function. Where do I insert the function
| and what arguments do I put in the ().
| You say that I should check to see that there are no spaces in the cell or
| non-printable characters. Each cell has a formula in it for example the first
| cell that I mentioned M3 when I click on the cell the formula is: =INDEX!F4
| and what is viewed is 7:30 AM. I don't know if this is what you are talking
| about when you say non-printable characters.
|
| When I run the LEN function on cell M3 I get a result of "6" for the entry
| 7:30 AM. The result is 0.3125. When I run the LEN function on Cell M11 I get
| a result of "7" for the entry 8:15 AM. The result is 0.34375.
|
| I got the numbers 0.3125 and 0.34375 when I ran the TRIM() function. I
| really am not sure what you are talking about with the CHAR(160) and replace
| with"". Where do I enter this function when I entered it into Cell M3 the
| result was: =INDEX!F4+CHAR(160).
|
| Also when I used the ISTEXT(), TRIM() functions I ran them in empty cells I
| don't know if I was suppose to run them in the cell where the problem is
| M3,M11.
|
| If you could help clarify this for me that would be great. Toppers said that
| he ran this function and he had no problem and I don't know how he did that .
|
| Please help
| Thanks
| Bill_De
|
|
|
|
| "Niek Otten" wrote:
|
| > Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't help.
You
| > can test with the ISTEXT() function. Right? You probably copied the times from another application.
| > Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of
characters
| > and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160)
character
| > with "" (nothing).
| > Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| >
| > | Hi Toppers
| > | I rewrote the function just as you wrote it and I still get the #Value!
| > | error message. When I clicked on the trace error the blue line went to the
| > | two cells in my main worksheet (M3,M11) with the message "A value in the
| > | formula is of the wrong data type". I checked the format for the two cells
| > | (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
| > | Worksheet DE2 M3= worng data type and M11= wrong data type even though both
| > | are set for time.
| > | I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
| > | (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
| > | Gameint) the result on enter was 8:05 AM.
| > |
| > | I checked the possible causes in help and I can't figure out what I am doing
| > | wrong based on what help showed. Here are the possible causes:
| > | -Entering text when the formula requires a number or a logical value, such
| > | as true or false.
| > | -entering or editing an array formula and then pressing enter.
| > | -Entering a cell reference, a formula, or a function as an array constant.
| > | -Supplying a range to an operator or a function that requires a single
| > | value, not a range.
| > | -Using a matrix that is not valid in one of the matrix worksheet functions.
| > | -Running a macro that enters a function that returns #value!
| > |
| > | Any suggestions on which way to go next. I just don't know which one of the
| > | possible problems it could be. By the way sorry to be such a bother,
| > | hopefully you can help me solve this problem and I will be out of your hair.
| > |
| > | Thanks again for all the help
| > | Bill_De
| > |
| > | "Toppers" wrote:
| > |
| > | > 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
| > | > >
| >
| >
| >

 

[Niek Otten]

There is one more thing I'd like to point out: you refer to cells in a worksheet directly from within the function (M20:M23, AT5).
That is not a good practice. You should include all input to the function in the argument list, just like Time1 and Time2. If you
don't, Excel will not be aware of the need for recalculation if you change any of those cells (how could it?). Very tricky!

--
Kind regards,

Niek Otten


|I tested Topper's code too, and it give results, not an error.
|
| 1. Did you copy and paste Topper's code, not just retype it? A typo can be easily overseen
| 2. Enter these formulas in empty cells on the INDEX sheet:
| =ISTEXT(M20)
| =ISTEXT(M21)
| =ISTEXT(M22)
| =ISTEXT(M23)
| =ISTEXT(AT5)
|
| They should all give FALSE as a result.
|
| I'm sure we will get this right!
| --
| Kind regards,
|
| Niek Otten
|
|| Thanks for the quick response. However I am very new at this Excel stuff and
|| am just learning about functions and writing code and all that stuff and I
|| have to say you have lost me some what.
|| I would like to reply to your response;
|| First I set the format when I created the document so the formating wasn't
|| done afterward it was done before I created the custom function. As far as
|| the times go they are not copied from another application the are
|| transferred/copied from another worksheet in the same workbook. I have a page
|| titled Index where I can enter all of the begining times and intervals and
|| then those time are sent to the first time slot of each game page.
|| Second there is a space between the numbers and the AM/PM but this is done
|| in the formating not by me. If I type in 7:30AM when I tab to another cell
|| the entry changes to 7:30 AM. Also if I overwrite the formating and enter
|| 7:30AM then all of the other entries on the work sheet change to #Value!, and
|| when I change the entry back to 7:30 AM the formula works correct.
|| Next you lost me with the ISTEXT() function. Where do I insert the function
|| and what arguments do I put in the ().
|| You say that I should check to see that there are no spaces in the cell or
|| non-printable characters. Each cell has a formula in it for example the first
|| cell that I mentioned M3 when I click on the cell the formula is: =INDEX!F4
|| and what is viewed is 7:30 AM. I don't know if this is what you are talking
|| about when you say non-printable characters.
||
|| When I run the LEN function on cell M3 I get a result of "6" for the entry
|| 7:30 AM. The result is 0.3125. When I run the LEN function on Cell M11 I get
|| a result of "7" for the entry 8:15 AM. The result is 0.34375.
||
|| I got the numbers 0.3125 and 0.34375 when I ran the TRIM() function. I
|| really am not sure what you are talking about with the CHAR(160) and replace
|| with"". Where do I enter this function when I entered it into Cell M3 the
|| result was: =INDEX!F4+CHAR(160).
||
|| Also when I used the ISTEXT(), TRIM() functions I ran them in empty cells I
|| don't know if I was suppose to run them in the cell where the problem is
|| M3,M11.
||
|| If you could help clarify this for me that would be great. Toppers said that
|| he ran this function and he had no problem and I don't know how he did that .
||
|| Please help
|| Thanks
|| Bill_De
||
||
||
||
|| "Niek Otten" wrote:
||
|| > Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't help.
| You
|| > can test with the ISTEXT() function. Right? You probably copied the times from another application.
|| > Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of
| characters
|| > and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160)
| character
|| > with "" (nothing).
|| > Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).
|| >
|| > --
|| > Kind regards,
|| >
|| > Niek Otten
|| >
|| > | Hi Toppers
|| > | I rewrote the function just as you wrote it and I still get the #Value!
|| > | error message. When I clicked on the trace error the blue line went to the
|| > | two cells in my main worksheet (M3,M11) with the message "A value in the
|| > | formula is of the wrong data type". I checked the format for the two cells
|| > | (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
|| > | Worksheet DE2 M3= worng data type and M11= wrong data type even though both
|| > | are set for time.
|| > | I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
|| > | (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
|| > | Gameint) the result on enter was 8:05 AM.
|| > |
|| > | I checked the possible causes in help and I can't figure out what I am doing
|| > | wrong based on what help showed. Here are the possible causes:
|| > | -Entering text when the formula requires a number or a logical value, such
|| > | as true or false.
|| > | -entering or editing an array formula and then pressing enter.
|| > | -Entering a cell reference, a formula, or a function as an array constant.
|| > | -Supplying a range to an operator or a function that requires a single
|| > | value, not a range.
|| > | -Using a matrix that is not valid in one of the matrix worksheet functions.
|| > | -Running a macro that enters a function that returns #value!
|| > |
|| > | Any suggestions on which way to go next. I just don't know which one of the
|| > | possible problems it could be. By the way sorry to be such a bother,
|| > | hopefully you can help me solve this problem and I will be out of your hair.
|| > |
|| > | Thanks again for all the help
|| > | Bill_De
|| > |
|| > | "Toppers" wrote:
|| > |
|| > | > 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
|| > | > >
|| >
|| >
|| >
|
|

 

[Bill_De]

Niek - It worked.

I think there was something wrong with the module in the workbook. I double
checked the entire function and it was exactly like Toppers. I then ran the
ISTEXT() function on all of the cells and everything came back "False",
However I was still getting the #VALUE! error. I then created a new test
workbook and entered Toppers function exactly as he gave it to me and the
function worked perfect. I went back to my competition workbook and
removed/deleted the module and inserted a new one. I then copied the function
from the test book to the competition book and changed the cell references
that needed to be changed and it work just like it was suppose to.
I can't thank you guys enough for taking the time to help me out on this I
know there are a lot of other people that need help and you took the time to
work with me.

Niek you are right also about Excel not being able to calculate the function
when I change an value in the cells (M20,M21,M22,M23,AT5) however this
function is going to be inserted into 444 cells on 22 worksheets.
Do you have any suggestions how I can have this function set up so that when
I change a value in the INDEX it will change the values in the function I
don't know if that is possible, but that is why I was trying to automate this
in the first place because there are so many slots that times would have to
be entered into. Not that I will be using all 22 worksheets at a time but I
will be using at least 12 to 15 and there are 22 cells in each worksheet that
need to have times entered in them.

If you have a way that I can get the above cells to change in the function
then I will be finished with the project.
This will probably be the last time we will be communicating on this subject
so again I want to thank you and Toppers so very much for your help. I never
could have completed this without it.

There is one more thing I'd like to point out: you refer to cells in a worksheet directly from within the function (M20:M23, AT5).
That is not a good practice. You should include all input to the function in the argument list, just like Time1 and Time2. If you
don't, Excel will not be aware of the need for recalculation if you change any of those cells (how could it?). Very tricky!

--
Kind regards,

Niek Otten


|I tested Topper's code too, and it give results, not an error.
|
| 1. Did you copy and paste Topper's code, not just retype it? A typo can be easily overseen
| 2. Enter these formulas in empty cells on the INDEX sheet:
| =ISTEXT(M20)
| =ISTEXT(M21)
| =ISTEXT(M22)
| =ISTEXT(M23)
| =ISTEXT(AT5)
|
| They should all give FALSE as a result.
|
| I'm sure we will get this right!
| --
| Kind regards,
|
| Niek Otten
|
|| Thanks for the quick response. However I am very new at this Excel stuff and
|| am just learning about functions and writing code and all that stuff and I
|| have to say you have lost me some what.
|| I would like to reply to your response;
|| First I set the format when I created the document so the formating wasn't
|| done afterward it was done before I created the custom function. As far as
|| the times go they are not copied from another application the are
|| transferred/copied from another worksheet in the same workbook. I have a page
|| titled Index where I can enter all of the begining times and intervals and
|| then those time are sent to the first time slot of each game page.
|| Second there is a space between the numbers and the AM/PM but this is done
|| in the formating not by me. If I type in 7:30AM when I tab to another cell
|| the entry changes to 7:30 AM. Also if I overwrite the formating and enter
|| 7:30AM then all of the other entries on the work sheet change to #Value!, and
|| when I change the entry back to 7:30 AM the formula works correct.
|| Next you lost me with the ISTEXT() function. Where do I insert the function
|| and what arguments do I put in the ().
|| You say that I should check to see that there are no spaces in the cell or
|| non-printable characters. Each cell has a formula in it for example the first
|| cell that I mentioned M3 when I click on the cell the formula is: =INDEX!F4
|| and what is viewed is 7:30 AM. I don't know if this is what you are talking
|| about when you say non-printable characters.
||
|| When I run the LEN function on cell M3 I get a result of "6" for the entry
|| 7:30 AM. The result is 0.3125. When I run the LEN function on Cell M11 I get
|| a result of "7" for the entry 8:15 AM. The result is 0.34375.
||
|| I got the numbers 0.3125 and 0.34375 when I ran the TRIM() function. I
|| really am not sure what you are talking about with the CHAR(160) and replace
|| with"". Where do I enter this function when I entered it into Cell M3 the
|| result was: =INDEX!F4+CHAR(160).
||
|| Also when I used the ISTEXT(), TRIM() functions I ran them in empty cells I
|| don't know if I was suppose to run them in the cell where the problem is
|| M3,M11.
||
|| If you could help clarify this for me that would be great. Toppers said that
|| he ran this function and he had no problem and I don't know how he did that .
||
|| Please help
|| Thanks
|| Bill_De
||
||
||
||
|| "Niek Otten" wrote:
||
|| > Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't help.
| You
|| > can test with the ISTEXT() function. Right? You probably copied the times from another application.
|| > Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of
| characters
|| > and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160)
| character
|| > with "" (nothing).
|| > Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).
|| >
|| > --
|| > Kind regards,
|| >
|| > Niek Otten
|| >
|| > | Hi Toppers
|| > | I rewrote the function just as you wrote it and I still get the #Value!
|| > | error message. When I clicked on the trace error the blue line went to the
|| > | two cells in my main worksheet (M3,M11) with the message "A value in the
|| > | formula is of the wrong data type". I checked the format for the two cells
|| > | (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
|| > | Worksheet DE2 M3= worng data type and M11= wrong data type even though both
|| > | are set for time.
|| > | I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
|| > | (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
|| > | Gameint) the result on enter was 8:05 AM.
|| > |
|| > | I checked the possible causes in help and I can't figure out what I am doing
|| > | wrong based on what help showed. Here are the possible causes:
|| > | -Entering text when the formula requires a number or a logical value, such
|| > | as true or false.
|| > | -entering or editing an array formula and then pressing enter.
|| > | -Entering a cell reference, a formula, or a function as an array constant.
|| > | -Supplying a range to an operator or a function that requires a single
|| > | value, not a range.
|| > | -Using a matrix that is not valid in one of the matrix worksheet functions.
|| > | -Running a macro that enters a function that returns #value!
|| > |
|| > | Any suggestions on which way to go next. I just don't know which one of the
|| > | possible problems it could be. By the way sorry to be such a bother,
|| > | hopefully you can help me solve this problem and I will be out of your hair.
|| > |
|| > | Thanks again for all the help
|| > | Bill_De
|| > |
|| > | "Toppers" wrote:
|| > |
|| > | > 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
|| > | > >
|| >
|| >
|| >
|
|

 

[Bill_De]

FOR TOPPERS

Toppers,
Could you check out the reply I sent to Niek's dated 4-21-06 If you have any
suggestions on how to get the cells to update in the function would be great.
Thanks for all your help I couldn't have finished this project without youguys

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

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

 

[Niek Otten]

Function Evtime2(Time1, Time2, gameint, Lunchbk, Addtm, Pmstart, Gametm)
Dim Result1
Dim Exp1, Exp2, Exp3, Exp4

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 Evtime2 = Result1 Else: Evtime2 = Exp4
End Function

The worksheet formula:

=evtime2(A6,B6,M20,M21,M22,M23,AT5)

This seems to give the same results as yours (Topper's) and it should be volatile to changes in the standard times.

Please check carefully!

To make it fool-proof and perform fast you would have to pay some attention to data types (DIMs), validation and error handling.
But you've got the basic idea, I think.

BTW, always include Option Explicit as the first line in your module. This can be done automatically in the VBE with
Tools>Options>Edit, Require Variable Declaration. Strongly recommended, should have been the default option anyway.

--
Kind regards,

Niek Otten



| Niek - It worked.
|
| I think there was something wrong with the module in the workbook. I double
| checked the entire function and it was exactly like Toppers. I then ran the
| ISTEXT() function on all of the cells and everything came back "False",
| However I was still getting the #VALUE! error. I then created a new test
| workbook and entered Toppers function exactly as he gave it to me and the
| function worked perfect. I went back to my competition workbook and
| removed/deleted the module and inserted a new one. I then copied the function
| from the test book to the competition book and changed the cell references
| that needed to be changed and it work just like it was suppose to.
| I can't thank you guys enough for taking the time to help me out on this I
| know there are a lot of other people that need help and you took the time to
| work with me.
|
| Niek you are right also about Excel not being able to calculate the function
| when I change an value in the cells (M20,M21,M22,M23,AT5) however this
| function is going to be inserted into 444 cells on 22 worksheets.
| Do you have any suggestions how I can have this function set up so that when
| I change a value in the INDEX it will change the values in the function I
| don't know if that is possible, but that is why I was trying to automate this
| in the first place because there are so many slots that times would have to
| be entered into. Not that I will be using all 22 worksheets at a time but I
| will be using at least 12 to 15 and there are 22 cells in each worksheet that
| need to have times entered in them.
|
| If you have a way that I can get the above cells to change in the function
| then I will be finished with the project.
| This will probably be the last time we will be communicating on this subject
| so again I want to thank you and Toppers so very much for your help. I never
| could have completed this without it.
|
| "Niek Otten" wrote:
|
| > There is one more thing I'd like to point out: you refer to cells in a worksheet directly from within the function (M20:M23,
AT5).
| > That is not a good practice. You should include all input to the function in the argument list, just like Time1 and Time2. If
you
| > don't, Excel will not be aware of the need for recalculation if you change any of those cells (how could it?). Very tricky!
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| >
| >
| > |I tested Topper's code too, and it give results, not an error.
| > |
| > | 1. Did you copy and paste Topper's code, not just retype it? A typo can be easily overseen
| > | 2. Enter these formulas in empty cells on the INDEX sheet:
| > | =ISTEXT(M20)
| > | =ISTEXT(M21)
| > | =ISTEXT(M22)
| > | =ISTEXT(M23)
| > | =ISTEXT(AT5)
| > |
| > | They should all give FALSE as a result.
| > |
| > | I'm sure we will get this right!
| > | --
| > | Kind regards,
| > |
| > | Niek Otten
| > |
| > || Thanks for the quick response. However I am very new at this Excel stuff and
| > || am just learning about functions and writing code and all that stuff and I
| > || have to say you have lost me some what.
| > || I would like to reply to your response;
| > || First I set the format when I created the document so the formating wasn't
| > || done afterward it was done before I created the custom function. As far as
| > || the times go they are not copied from another application the are
| > || transferred/copied from another worksheet in the same workbook. I have a page
| > || titled Index where I can enter all of the begining times and intervals and
| > || then those time are sent to the first time slot of each game page.
| > || Second there is a space between the numbers and the AM/PM but this is done
| > || in the formating not by me. If I type in 7:30AM when I tab to another cell
| > || the entry changes to 7:30 AM. Also if I overwrite the formating and enter
| > || 7:30AM then all of the other entries on the work sheet change to #Value!, and
| > || when I change the entry back to 7:30 AM the formula works correct.
| > || Next you lost me with the ISTEXT() function. Where do I insert the function
| > || and what arguments do I put in the ().
| > || You say that I should check to see that there are no spaces in the cell or
| > || non-printable characters. Each cell has a formula in it for example the first
| > || cell that I mentioned M3 when I click on the cell the formula is: =INDEX!F4
| > || and what is viewed is 7:30 AM. I don't know if this is what you are talking
| > || about when you say non-printable characters.
| > ||
| > || When I run the LEN function on cell M3 I get a result of "6" for the entry
| > || 7:30 AM. The result is 0.3125. When I run the LEN function on Cell M11 I get
| > || a result of "7" for the entry 8:15 AM. The result is 0.34375.
| > ||
| > || I got the numbers 0.3125 and 0.34375 when I ran the TRIM() function. I
| > || really am not sure what you are talking about with the CHAR(160) and replace
| > || with"". Where do I enter this function when I entered it into Cell M3 the
| > || result was: =INDEX!F4+CHAR(160).
| > ||
| > || Also when I used the ISTEXT(), TRIM() functions I ran them in empty cells I
| > || don't know if I was suppose to run them in the cell where the problem is
| > || M3,M11.
| > ||
| > || If you could help clarify this for me that would be great. Toppers said that
| > || he ran this function and he had no problem and I don't know how he did that .
| > ||
| > || Please help
| > || Thanks
| > || Bill_De
| > ||
| > ||
| > ||
| > ||
| > || "Niek Otten" wrote:
| > ||
| > || > Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't
help.
| > | You
| > || > can test with the ISTEXT() function. Right? You probably copied the times from another application.
| > || > Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of
| > | characters
| > || > and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160)
| > | character
| > || > with "" (nothing).
| > || > Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).
| > || >
| > || > --
| > || > Kind regards,
| > || >
| > || > Niek Otten
| > || >
| > || > | Hi Toppers
| > || > | I rewrote the function just as you wrote it and I still get the #Value!
| > || > | error message. When I clicked on the trace error the blue line went to the
| > || > | two cells in my main worksheet (M3,M11) with the message "A value in the
| > || > | formula is of the wrong data type". I checked the format for the two cells
| > || > | (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
| > || > | Worksheet DE2 M3= worng data type and M11= wrong data type even though both
| > || > | are set for time.
| > || > | I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
| > || > | (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
| > || > | Gameint) the result on enter was 8:05 AM.
| > || > |
| > || > | I checked the possible causes in help and I can't figure out what I am doing
| > || > | wrong based on what help showed. Here are the possible causes:
| > || > | -Entering text when the formula requires a number or a logical value, such
| > || > | as true or false.
| > || > | -entering or editing an array formula and then pressing enter.
| > || > | -Entering a cell reference, a formula, or a function as an array constant.
| > || > | -Supplying a range to an operator or a function that requires a single
| > || > | value, not a range.
| > || > | -Using a matrix that is not valid in one of the matrix worksheet functions.
| > || > | -Running a macro that enters a function that returns #value!
| > || > |
| > || > | Any suggestions on which way to go next. I just don't know which one of the
| > || > | possible problems it could be. By the way sorry to be such a bother,
| > || > | hopefully you can help me solve this problem and I will be out of your hair.
| > || > |
| > || > | Thanks again for all the help
| > || > | Bill_De
| > || > |
| > || > | "Toppers" wrote:
| > || > |
| > || > | > 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
| > || > | > >
| > || >
| > || >
| > || >
| > |
| > |
| >
| >
| >

 

[Bill_De]

I tried the function and formula and it does work but there is one problem
and it is the same problem that I had when I wrote out the fucntion as a long
formula and that is it does not read or calculate the first two if statements
(If Exp1<Exp2, If Exp1>Exp2).

I noticed this as I got into entering the function about 5 levels. So I
checked it out. If I delete the first 2 if statements then F2, Enter I get
the same result as if the two statements were in. If I delete the 3rd if
statement (If Exp1 > Exp3) then F2 Enter The cell changes to 12:00 AM and no
matter what I do with the function, formula, or cell enteries it will not
change from 12:00 AM.

I think but this is only a guess that it has something to do with the
=Result1 entries, but like I said it is only a guess, or perhaps it's
impossible to do 3 if statements in Excel. Like I said I had the same problem
when I had this in just a formula which is why I tried going to a custom
function. But the time did change like you said it would.

Here is what I had before I tried a custom function:
=If(AND(SUM(M27,$AT$5,INDEX!$M$20)>(INDEX!$M$21-$AT$5),INDEX!$M$23,SUM(M27,$AT$5,INDEX!$M$20)),+IF(SUM(M27,$AT$5,INDEX!M$20)<INDEX!$M$23,SUM(M23,$AT$5,INDEX!$M$20))+IF(SUM(M27,$AT$5,INDEX!$M$20),sUM(M3,$AT$5,INDEX!$M$200,SUM(M27,$AT$5,INDEX!$M$20,INDEX!$M$22),SUM(M27,$AT$5INDEX!$M$20)))

And with this one it would only perform the third if statement and bypass
the first two if statements.

Anyway thanks for all the effort and help.

Bill_D

Function Evtime2(Time1, Time2, gameint, Lunchbk, Addtm, Pmstart, Gametm)
Dim Result1
Dim Exp1, Exp2, Exp3, Exp4

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 Evtime2 = Result1 Else: Evtime2 = Exp4
End Function

The worksheet formula:

=evtime2(A6,B6,M20,M21,M22,M23,AT5)

This seems to give the same results as yours (Topper's) and it should be volatile to changes in the standard times.

Please check carefully!

To make it fool-proof and perform fast you would have to pay some attention to data types (DIMs), validation and error handling.
But you've got the basic idea, I think.

BTW, always include Option Explicit as the first line in your module. This can be done automatically in the VBE with
Tools>Options>Edit, Require Variable Declaration. Strongly recommended, should have been the default option anyway.

--
Kind regards,

Niek Otten



| Niek - It worked.
|
| I think there was something wrong with the module in the workbook. I double
| checked the entire function and it was exactly like Toppers. I then ran the
| ISTEXT() function on all of the cells and everything came back "False",
| However I was still getting the #VALUE! error. I then created a new test
| workbook and entered Toppers function exactly as he gave it to me and the
| function worked perfect. I went back to my competition workbook and
| removed/deleted the module and inserted a new one. I then copied the function
| from the test book to the competition book and changed the cell references
| that needed to be changed and it work just like it was suppose to.
| I can't thank you guys enough for taking the time to help me out on this I
| know there are a lot of other people that need help and you took the time to
| work with me.
|
| Niek you are right also about Excel not being able to calculate the function
| when I change an value in the cells (M20,M21,M22,M23,AT5) however this
| function is going to be inserted into 444 cells on 22 worksheets.
| Do you have any suggestions how I can have this function set up so that when
| I change a value in the INDEX it will change the values in the function I
| don't know if that is possible, but that is why I was trying to automate this
| in the first place because there are so many slots that times would have to
| be entered into. Not that I will be using all 22 worksheets at a time but I
| will be using at least 12 to 15 and there are 22 cells in each worksheet that
| need to have times entered in them.
|
| If you have a way that I can get the above cells to change in the function
| then I will be finished with the project.
| This will probably be the last time we will be communicating on this subject
| so again I want to thank you and Toppers so very much for your help. I never
| could have completed this without it.
|
| "Niek Otten" wrote:
|
| > There is one more thing I'd like to point out: you refer to cells in a worksheet directly from within the function (M20:M23,
AT5).
| > That is not a good practice. You should include all input to the function in the argument list, just like Time1 and Time2. If
you
| > don't, Excel will not be aware of the need for recalculation if you change any of those cells (how could it?). Very tricky!
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| >
| >
| > |I tested Topper's code too, and it give results, not an error.
| > |
| > | 1. Did you copy and paste Topper's code, not just retype it? A typo can be easily overseen
| > | 2. Enter these formulas in empty cells on the INDEX sheet:
| > | =ISTEXT(M20)
| > | =ISTEXT(M21)
| > | =ISTEXT(M22)
| > | =ISTEXT(M23)
| > | =ISTEXT(AT5)
| > |
| > | They should all give FALSE as a result.
| > |
| > | I'm sure we will get this right!
| > | --
| > | Kind regards,
| > |
| > | Niek Otten
| > |
| > || Thanks for the quick response. However I am very new at this Excel stuff and
| > || am just learning about functions and writing code and all that stuff and I
| > || have to say you have lost me some what.
| > || I would like to reply to your response;
| > || First I set the format when I created the document so the formating wasn't
| > || done afterward it was done before I created the custom function. As far as
| > || the times go they are not copied from another application the are
| > || transferred/copied from another worksheet in the same workbook. I have a page
| > || titled Index where I can enter all of the begining times and intervals and
| > || then those time are sent to the first time slot of each game page.
| > || Second there is a space between the numbers and the AM/PM but this is done
| > || in the formating not by me. If I type in 7:30AM when I tab to another cell
| > || the entry changes to 7:30 AM. Also if I overwrite the formating and enter
| > || 7:30AM then all of the other entries on the work sheet change to #Value!, and
| > || when I change the entry back to 7:30 AM the formula works correct.
| > || Next you lost me with the ISTEXT() function. Where do I insert the function
| > || and what arguments do I put in the ().
| > || You say that I should check to see that there are no spaces in the cell or
| > || non-printable characters. Each cell has a formula in it for example the first
| > || cell that I mentioned M3 when I click on the cell the formula is: =INDEX!F4
| > || and what is viewed is 7:30 AM. I don't know if this is what you are talking
| > || about when you say non-printable characters.
| > ||
| > || When I run the LEN function on cell M3 I get a result of "6" for the entry
| > || 7:30 AM. The result is 0.3125. When I run the LEN function on Cell M11 I get
| > || a result of "7" for the entry 8:15 AM. The result is 0.34375.
| > ||
| > || I got the numbers 0.3125 and 0.34375 when I ran the TRIM() function. I
| > || really am not sure what you are talking about with the CHAR(160) and replace
| > || with"". Where do I enter this function when I entered it into Cell M3 the
| > || result was: =INDEX!F4+CHAR(160).
| > ||
| > || Also when I used the ISTEXT(), TRIM() functions I ran them in empty cells I
| > || don't know if I was suppose to run them in the cell where the problem is
| > || M3,M11.
| > ||
| > || If you could help clarify this for me that would be great. Toppers said that
| > || he ran this function and he had no problem and I don't know how he did that .
| > ||
| > || Please help
| > || Thanks
| > || Bill_De
| > ||
| > ||
| > ||
| > ||
| > || "Niek Otten" wrote:
| > ||
| > || > Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't
help.
| > | You
| > || > can test with the ISTEXT() function. Right? You probably copied the times from another application.
| > || > Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of
| > | characters
| > || > and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160)
| > | character
| > || > with "" (nothing).
| > || > Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).
| > || >
| > || > --
| > || > Kind regards,
| > || >
| > || > Niek Otten
| > || >
| > || > | Hi Toppers
| > || > | I rewrote the function just as you wrote it and I still get the #Value!
| > || > | error message. When I clicked on the trace error the blue line went to the
| > || > | two cells in my main worksheet (M3,M11) with the message "A value in the
| > || > | formula is of the wrong data type". I checked the format for the two cells
| > || > | (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
| > || > | Worksheet DE2 M3= worng data type and M11= wrong data type even though both
| > || > | are set for time.
| > || > | I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
| > || > | (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
| > || > | Gameint) the result on enter was 8:05 AM.
| > || > |
| > || > | I checked the possible causes in help and I can't figure out what I am doing
| > || > | wrong based on what help showed. Here are the possible causes:
| > || > | -Entering text when the formula requires a number or a logical value, such
| > || > | as true or false.
| > || > | -entering or editing an array formula and then pressing enter.
| > || > | -Entering a cell reference, a formula, or a function as an array constant.
| > || > | -Supplying a range to an operator or a function that requires a single
| > || > | value, not a range.
| > || > | -Using a matrix that is not valid in one of the matrix worksheet functions.
| > || > | -Running a macro that enters a function that returns #value!
| > || > |
| > || > | Any suggestions on which way to go next. I just don't know which one of the
| > || > | possible problems it could be. By the way sorry to be such a bother,
| > || > | hopefully you can help me solve this problem and I will be out of your hair.
| > || > |
| > || > | Thanks again for all the help
| > || > | Bill_De
| > || > |
| > || > | "Toppers" wrote:
| > || > |
| > || > | > 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
| > || > | > >
| > || >
| > || >
| > || >
| > |
| > |
| >
| >
| >