Excel Pasting Time / Manipulation - Help!

[horlbecm]

I am having a problems in with relation to time formats and would greatly appreciate any help.

The excel file I am writing the code for uses a Userform to prompt the user to select a file with various data - .txt format. I have to use the delimited options to preserve all the required data in separate cells (i.e. I can't paste into clipboard and then bring it into Excel as text). The problem is that the source program for the .txt file (which I have no control over) puts time into this format: 456:42:00 (example).

This represents 04:56:42 in UTC / 24hr time. The time is correct as displayed, but I need to reference the time in other subs to compare the times between multiple lines. When I try to use any time or time/date based formula ("Hours()", "Minutes()", "Seconds()", "DateDiff()", etc...) it reads the spot with "456" (sometimes 4 digits depending on the time) as hours (although it is actually both hours and minutes), the spot with "42" as minutes (which is actually seconds) and finally the spot with "00" as seconds, which is actually 'spoofer' info. Again, throw all the sarcastic comments you want at the format the time is spit out in, but that is the file format I need to work with.

I've tried the following:
Setting the .numberformat to "hh:mm:ss" or "[h]:mm:ss"
Setting .value = "=left()", as well as the Mid, and Right formulas
All of these just corrupts the time, since it doesn't see the hours, minutes and seconds in the right spot.

I also tried setting .text instead of .value with the left/right and mid, but get and "Object Required" error.

Any help? I tried to make it a textual explanation since the coding gets complicated.

[Stewart Ross]

It is most unfortunate that the exported data combines hours and minutes. In doing so the internal time value which Excel uses to represent the time will be completely incorrect - as you pointed out using your example of 456:00 which Excel simply cannot interpret in any meaningful way. Worse, because it is being stored as a date/time value after it is read into Excel you cannot manipulate it with string functions the way you have tried to do - nor can you convert it to a string easily because of the way Excel has tried to interpret the values it is seeing as hours but which are way out of range.

If you cannot get the output time format changed to standard hh:mm (or even into a straightforward single numeric value) I reckon you may have to pre-process your delimited text file in one of the following ways:

1. try prefixing the time field with a single apostrophe to force Excel to treat the value as text - at least then you could use string functions to extract the hours-minutes combination for subsequent separation, or

2. use a preprocessing routine to replace the non-standard time value with one that makes sense before it gets to Excel, or

3. design a custom VBA routine to read the whole of the delimited text file into the Excel sheet, converting the non-standard time value as it is read in. This is not as onerous as it sounds - but it is still a fair bit of work to loop through all lines of the file and split each line into its individual components.

Otherwise you will have to come up with a way to convert the meaningless date/time values to meaningful ones - but so far I've not come up with a viable way to rescue the dud values once they are in the sheet in date/time form.

-Stewart

[kadghar]

Oh, you'll find really interesting the string manipulation, and will help you to have exaclty what you want.

First of all, you do have control of your txt file, just google the syntax for OPEN / FOR / AS. This will help you import the text file into a string, or into a string array.

Since you're working in excel, you can use excel's dialogs to search the txt file. That'll really help you a lot. Google the APPLICATION.FILEDIALOG syntax

Once you have your string, you can use INSTR, MID, REPLACE, etc. to extract the numbers you want, and then asign them to a DATE variable.

e.g.

public sub ChangeTime(byref Str1 as string)
    str1 = Replace(str1, ":", "")
    str1 = Right(str1, Len(str1) - 2)
    str1 = Mid(str1, 1, Len(str1) - 4) & ":" & _
           Mid(str1, Len(str1) - 3, 2) & ":" & _
           Right(str1, 2)
end sub

HTH