Excel: Imported time hh:mm.0 to hh:mm

[etseel]

I have a .csv file that is downloaded from a vendor on a daily basis, with multiple tables include on the same page, with varying lengths. I need to get at the data in one of the tables but I can't format the columns for the entire file, since there are multiple data columns of various types within the same file. So I just accept the default formatting provided by importing the CSV into a single sheet.

Unfortunately the vendor formats their (24hr) times as hh.mm.0 Excel 2000 doesn't properly recognize the format and treats all the times as mm:ss.0 and therefore adds 12: in front of all my times. So if the vendor provides 16:32.0 which is 4:32pm - excel treats it as 12:16:32.0 I don't care about anything after the decimal, and only need HH:MM

HOW can I format my times to HH:MM and get the correct hour? I want to accomplish this during a formattting VB script that finds and copies the report of interest to the appropriate destination worksheet.

Thanks!

 

[AnotherHiggins]

If what you have is mm:ss and you want hh:mm, just multiply by 60. You can do this in a new column or copy the number 60 then use Edit > Paste Special > Multiply.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

etseel,

FYI...

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[etseel]

SkipVought - I think dates/times are a problem because all the users care about is the readability of the output document/application. The formatting is critical to giving the user the perception that they have the right information they need to make decisions from the data; even if they had the right data prior to formatting. Seems like people would rather wait longer to get information they can comfortably read instead of quickly getting data they find difficult to interpret...

Interesting that Excel wouldn't properly interpret my custom formatting hh:mm.0

Anyway, the multiply by 60 trick seems to solve my problem. I'll write it into the VB and see if it works. Thanks to anotherhiggins!