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

"Bill_De" wrote in message ...
| 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
|
| "Bill_De" wrote in message ...
| | 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
| |
|
|
|