Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I seem to have an inexplicable error which I hope someone can help me solve.
I am using Excel 2003 running on a Windows XP Professional platform. I wrote the following user-defined function to convert to seconds an elapsed time stored as a text string, BuildTime, which has the format dd:hh:mm:ss. Only the necessary positions in the string are filled, i.e., one minute is stored as 1:00, ten minutes as 10:00, one hour as 1:00:00, ten hours as 10:00:00, one day as 1:00:00:00, ten days as 10:00:00:00, etc. Values of less than one minute are stored as 0:ss. Therefore, one and only one of the procedure's "If StringLen =" tests is true for any value of BuildTime. The problem is that the procedure invariably returns a #VALUE! error when, and only when, StringLen = 8, and I am a completely baffled as to why. Any help would be greatly appreciated! Dim Days As Integer Dim Hours As Integer Dim Minutes As Integer Dim Seconds As Integer Dim StringLen As Integer Function TimeInSeconds(BuildTime As String) Days = 0 'Initializing these variables makes no difference; I was Hours = 0 'merely trying everything I could think of. Minutes = 0 Seconds = 0 StringLen = Len(BuildTime) If StringLen = 11 Then Days = Val(Mid(BuildTime, 1, 2)) Hours = Val(Mid(BuildTime, 4, 2)) Minutes = Val(Mid(BuildTime, 7, 2)) End If If StringLen = 10 Then Days = Val(Mid(BuildTime, 1, 1)) Hours = Val(Mid(BuildTime, 3, 2)) Minutes = Val(Mid(BuildTime, 6, 2)) End If If StringLen = 8 Then Days = 0 Hours = Val(Mid(BuildTime, 1, 2)) Minutes = Val(Mid(BuildTime, 4, 2)) End If If StringLen = 7 Then Days = 0 Hours = Val(Mid(BuildTime, 1, 1)) Minutes = Val(Mid(BuildTime, 3, 2)) End If If StringLen = 5 Then Days = 0 Hours = 0 Minutes = Val(Mid(BuildTime, 1, 2)) End If If StringLen = 4 Then Days = 0 Hours = 0 Minutes = Val(Mid(BuildTime, 1, 1)) End If Seconds = Val(Right(BuildTime, 2)) TimeInSeconds = (Days * 86400) + (Hours * 3600) + (Minutes * 60) + Seconds End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
String manipulation problem | Excel Programming | |||
Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a) | Excel Programming | |||
Importing Long String - String Manipulation (EDI EANCOM 96a) | Excel Programming | |||
String manipulation | Excel Programming | |||
Help with perplexing form problem | Excel Programming |