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

"Toppers" wrote:

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).

"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
|