time saved as text

[lezawang]

Hi
I am reading some articles and they say importing data from database/.csv files to excel the time usually converted to text and therefore I need to use timevalue() and other functions or formula to convert the time to a value. I tried to google to find such files/examples but could not find any. Do you know where I can download such files to practice change time saved as a text to time value? Thank you very much.

 

[joeu2004]

I am reading some articles and they say importing data from database/.csv files to excel the time usually converted to text and therefore I need to use timevalue() and other functions or formula to convert the time to a value.

Well, that is certainly incorrect for CSV files.

First, we must make a distinction between "opening" a CSV file and "importing" a CSV file. Do not confuse the two.

When we "import", the Text Import Wizard does give us the option to format each column as Text. Of course, if you do that, any time values will be input as text -- because you asked for that.

When we "open" a CSV file, Excel tries to interpret any text values as numeric. But that can be affected by your regional and language settings. Excel will recognize 12:34:56.789 as the numeric time 12h 34m 56.789s in most languages. It will recognize 34:56.0 as the numeric time 34m 56s.

As for databases.... I suspect that it depends on the interface between Excel and the database application, as well as how the data is stored in the database. I do not have any experience with that.

Do you know where I can download such files to practice change time saved as a text to time value?

Perhaps the easiest way is to open a new Excel file, enter the time data in various forms, then save as CSV.

To open the CSV file, double-click on the CSV icon in the file folder.

To import the CSV file, open a new Excel file and click Data > From Text, select the file and click Import, then follow the prompts (diaglog boxes) in the Import Text Wizard.

 

[joeu2004]

I am reading some articles and they say importing data from database/.csv files to excel the time usually converted to text and therefore I need to use timevalue() and other functions or formula to convert the time to a value.

Well, that is certainly incorrect for CSV files.

I want to reiterate the incorrectness of the assertion that you read, at least with respect to CSV files.

In fact, if TIMEVALUE works, it is also unnecessary because, AFAIK, Excel automatically recognizes and converts any time text that TIMEVALUE does.

The only time that TIMEVALUE would be necessary after-the-fact is if you import (not open) the CSV file and choose to format the column as Text in the final Import Text Wizard dialog box.

However, again, the success of Excel or TIMEVALUE depends on the form of the time string; in particular, whether it conforms to the syntax that Excel expects, which partly depends on regional and language options.

For example, an application might write time into the CSV file in the form 12.34 instead of 12:34 for 12h 34m. Excel will interpret that as 12 plus 34/100, which is numeric, but Excel time. On the other hand, Excel will not recognize and convert 12.34.56 as time for 12h 34m 56s; that will be input as text, or it might be convert to another number, depending on regional and language options.

Or an application might write 12:34P or 12:34PM. Excel recognizes only 12:34 PM or 12:34 pm, with a space in between.

An application might write 5:12:34 for 5d 12h 34m, which Excel interprets and converts to the unintended numeric time value 5h 12m 34m. And worse, an application might write 5 12:34, with a space in between, which Excel does not recognize as time; so it is indeed entered as text.

Finally, an application might write 5d 12h 34m or 12h 34m literally (with "h" and "m") into the CSV file. Excel does not recognize that automatically. For one way to convert that, see my response #4 in the thread at https://www.mrexcel.com/forum/excel-questions/1113545-format-convert-hours-minutes.html . For numeric time, remove the ROUND(...,0) wrapper and the "1440*" part.

In general, the Excel rules for recognizing and converting time text to numeric time are the same for reading CSV files as they are for manually typing it into a cell. So, you can experiment simply by manually entering the time text exactly as it might appear in the CSV file.

Again, I cannot say anything about data directly from databases, first, because I have no experience with it, and second, because it might depend on the interface between the database application and Excel.