Adding very large amounts of time together.

USAMax

Well-known Member
Joined
May 31, 2006
Messages
843
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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:

Code:
Application.WorksheetFunction.Days360(Range("K15"), Date)
 
Upvote 0
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:

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top