Home 
Search 
Today's Posts 
#1




Custom functions calculating time arguments Help Desperate
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=(LunchbkGametm) 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 
#2




Custom functions calculating time arguments Help Desperate
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=(LunchbkGametm) 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 
#3




Custom functions calculating time arguments Help Desperate
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=(LunchbkGametm) 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 
#4




Custom functions calculating time arguments Help Desperate
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 nonprintable 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). Reenter 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=(LunchbkGametm)  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  
#5




Custom functions calculating time arguments Help Desperate
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 nonprintable 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). Reenter 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=(LunchbkGametm)  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  
#6




Custom functions calculating time arguments Help Desperate
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 nonprintable 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 nonprintable 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 nonprintable 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). Reenter 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=(LunchbkGametm)  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  
#7




Custom functions calculating time arguments Help Desperate
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 nonprintable 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). Reenter 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=(LunchbkGametm)  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  
#8




Custom functions calculating time arguments Help Desperate
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  nonprintable 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 nonprintable 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 nonprintable 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).  Reenter 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=(LunchbkGametm)   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      
#9




Custom functions calculating time arguments Help Desperate
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 "Niek Otten" wrote in message ... 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  nonprintable 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 nonprintable 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 nonprintable 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).  Reenter 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=(LunchbkGametm)   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        
#10




Custom functions calculating time arguments Help Desperate
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 "Niek Otten" wrote in message ... 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  nonprintable 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 nonprintable 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 nonprintable 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).  Reenter 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=(LunchbkGametm)   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        
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Cells with time format and calculating the diffrence  New Users to Excel  
Calculating time and pay in excel  Excel Discussion (Misc queries)  
Calculating time between successful data transmissions  Excel Discussion (Misc queries)  
Calculating effective time from start/end date+time  Excel Worksheet Functions  
calculating date time ranges  Excel Worksheet Functions 