Adding very large amounts of time together.

[USAMax]

My program has been running for a while until I ran into a very large time from the data it is importing. I though some of the times were large but this one is in a new ball park. "10016:30:32"

I admit that is sloppy to declair a varable as varent but I am not sure how to deal with time. This is a simple line but fails when it hits this number as it is 416 days!

CPUTm = CPUTm + Cells(CurAppRow, CPCol)

I am sure this is simple for someone but this is a first for me.

 

[Jeffrey Smith]

Maybe convert your output to include days. Store a beginning Date and time. Subtract the current date and time and display 417 days, 8 hours, 30 minutes, 32 seconds. To calculate the difference between two dates:

Application.WorksheetFunction.Days360(Range("K15"), Date)

 

[USAMax]

Maybe convert your output to include days. Store a beginning Date and time. Subtract the current date and time and display 417 days, 8 hours, 30 minutes, 32 seconds. To calculate the difference between two dates:

Application.WorksheetFunction.Days360(Range("K15"), Date)

Converting everything for just one field in one group is a bit much when I am only using it in a calculation for a percent. When it is imported it is coming through as text so I will look at the import process unless someone has another idea. It just seams funny that it has a limit to the size of this number.

 

[USAMax]

Hey Jeffrey, I have tried your idea and several of the other Time and Date functions but because of the size it cannot convert the value.

With today's value of, "10207:18:38" and yesterday's value of, "10016:30:32" that brings up another question. What is the first value with a difference of 191 over 24 hours?
This is the, "System Idle Process" from task manager that I use to compare other services to.
It cannot be minutes because the server would have only been idle for just over 3 hours.
It cannot be hours because 7.9 days would have passed in the last 24 hours.

 

[barry houdini]

I don't have a solution for you but I believe the problem is that Excel doesn't recognise (input) time values greater than 9999:59:59 so it will treat 10016:30:32 as a text string. You might be able to test whether the value is a string or not - if it is then split out the last 8 characters from the rest, convert to numbers and then put back together again. If I was doing this on the worksheet then a text value of 10016:30:32 can be converted to a time value of 10016:30:32 with this formula

=RIGHT(A1,8)+LEFT(A1,3)*100/24

[assuming that the hours will be between 10,000 and 99,999]

 

[USAMax]

Here is what I came up with...

If IsNumeric(Cells(CurAppRow, CPCol)) Then
CPUTm = CPUTm + Cells(CurAppRow, CPCol) ' YES: Add the current CPU Time to the accumulator
Else
CPUTm = CPUTm + Left(Cells(CurAppRow, CPCol), InStr(1, Cells(CurAppRow, CPCol), ":") - 1) / 24 ' YES: Add the current CPU Time to the accumulator
End If

When the hours were grater than 9,999 the value could not be read so it saw it as some String value. When it was less than 10,000 it was the normal Time Date serial number so I checked to see if it was a numeric value and processed it accordingly but if it was a string I read the hours only and converted it into a serial number value.

What’s 10,000 hours, in reference to Einstein quote, it is all relative.