Hi,
I have an UDF function to calculate time. One of the SUM is not working, and I cannot fix It.
Please help me fix this UDF !
Take a look in the sample, SUM is not working in BQ3 cell (orange cell), It should SUM positive times in range, but It doesn't
I think you don`t need UDF. To sum only positive times use formula SUMIF. E.g.I have a question about your formula in column A. What is it calculating? Why do you work with time like with strings?
=SUMIF(BQ5:BQ20,">0")
Last edited by lancer102rus; 08-11-2014 at 03:41 AM.
Unfortunatelly that is not working, and tried that before posting here. Regular =SUM(BQ5:BQ20) doesn't work too.Originally Posted by lancer102rus
I think you don`t need UDF. To sum only positive times use formula SUMIF. E.g.=SUMIF(BQ5:BQ20,">0")
Formulas in column A and formulas with UDF function do the calculations for positive and negative times, that why strings.I have a question about your formula in column A. What is it calculating? Why do you work with time like with strings?
The primary problem is: BQ5 and BQ15 are strings ("21:00" and "24:00"), not numeric time. That causes errors in your UDF, which go undetected because you have On Error Resume Next.
Should you fix BQ5 and BQ15 (et al) so they are numeric time? Or do you need help to ensure that your UDF tolerates "time strings"?
Also note that BQ8, BQ13 and BQ18 are strings with one space (" "), not null strings (""). Those also cause errors. But they are are benign because of the On Error Resume Next.
Nevertheless, if that is unexpected, I suggest that you clean up your data.
[EDIT] PS.... It appears that you have "time strings" in column A so that you can represent negative time. It is unclear whether positive must also be "time strings". That is a design issue that only you can answer. Nevertheless, it appears that the UDF expects to work with the negative "time strings". So it should tolerate positive "time strings", too. So obviously, there is a design flaw in the UDF.
With positive "time strings", the runtime error arises because Brr=1 and Drr(0) is the string "21:00", for example. Thus, Int(Drr(0)) causes a "type mismatch" error. I'm sorry, but I don't have time now to study the UDF implementation and offer a fix.
Last edited by joeu2004; 08-11-2014 at 04:03 AM.
Maybe, but look in cell BR3 and all cells in range for SUM, you'll notice that UDF works fine with strings. Actually exact same formula in cells for SUM, just opposite one.The primary problem is: BQ5 and BQ15 are strings ("21:00" and "24:00"), not numeric time. That causes errors in your UDF, which go undetected because you have On Error Resume Next.
These are empty cells, you probably mean different ones ?Also note that BQ8, BQ13 and BQ18 are strings with one space (" "), not null strings (""). Those also cause errors. But they are are benign because of the On Error Resume Next.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks