Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Imported time text to seconds
I am importing time data, which imports in the hh:mm:ss format (or omitting
hh and mm if 0 value) to my eyes, but in the general format, so those entries which could represent a time of day are interpreted as a time of day in the AM, and that which could not be a time of day is interpreted as text. e.g. "4:00" in a cell shows as "4:00 AM" in the formular bar and :40 shows as :40 both places, but is formatted as text. Prior to import, and to the eyes after import in the cells themselves, there are five types of entries: 00:00:00 0:00:00 00:00 0:00 :00 How can I convert these text strings and time values to seconds? Can it be done? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Imported time text to seconds
"Literalgar" wrote:
those entries which could represent a time of day are interpreted as a time of day in the AM .... or PM, depending on the numbers. But whenever you have two colons (xx:xx:xx, x:xx:xx or 0:xx:xx), the data is indeed interpreted as hh:mm:ss. You can see this by formatting the cells with the Custom format "[h]:mm:ss" without quotes. The real problem arises with imported times of the form xx:xx or 0:xx (interpreted as hh:mm) and :xx, which is interpreted as text because it is not a valid time form. There are adjustments that you can make to each form after importing. For xx:xx and 0:xx, you could use copy-and-paste-special-divide 60. And for :xx, you could use a formula of the form =RIGHT(A1,2)/3600/24, and copy-and-paste-special-"value and number formats" to overwrite :xx. In both cases, format the resulting cell with the same Custom format above. But I doubt that that is the general solution you are looking for, since it requires that you be mindful of the original form or that you recognized the incorrect results and handle them individually. Perhaps a better solution for you would be to import the time field in Text format, not General. (You can do this using the Import Wizard.) Then use a macro to convert the forms. If you are interested in the details of the macro, indicate that in a response to this thread. Alternatively, use a formula of the following form in a parallel column of helper cells: =IF(LEN(A1)5, --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) Format those cells with the Custom format above, and copy-and paste-special-"value and number formats" to overwrite the original text cells. Then you can delete the helper cells. Note: The formula above assumes that you do __not__ have data of the form 1:2:3, meaning 1h 2m 3s. Your original posting suggests that the form of that data would be 1:02:03 instead. ----- original message ----- "Literalgar" wrote in message ... I am importing time data, which imports in the hh:mm:ss format (or omitting hh and mm if 0 value) to my eyes, but in the general format, so those entries which could represent a time of day are interpreted as a time of day in the AM, and that which could not be a time of day is interpreted as text. e.g. "4:00" in a cell shows as "4:00 AM" in the formular bar and :40 shows as :40 both places, but is formatted as text. Prior to import, and to the eyes after import in the cells themselves, there are five types of entries: 00:00:00 0:00:00 00:00 0:00 :00 How can I convert these text strings and time values to seconds? Can it be done? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Imported time text to seconds
Thank you JoeU2004. I will work with your suggestions, and let you know
(hopefully by the green check!!!!). Many thanks for the time and effort you put into your response. "JoeU2004" wrote: "Literalgar" wrote: those entries which could represent a time of day are interpreted as a time of day in the AM .... or PM, depending on the numbers. But whenever you have two colons (xx:xx:xx, x:xx:xx or 0:xx:xx), the data is indeed interpreted as hh:mm:ss. You can see this by formatting the cells with the Custom format "[h]:mm:ss" without quotes. The real problem arises with imported times of the form xx:xx or 0:xx (interpreted as hh:mm) and :xx, which is interpreted as text because it is not a valid time form. There are adjustments that you can make to each form after importing. For xx:xx and 0:xx, you could use copy-and-paste-special-divide 60. And for :xx, you could use a formula of the form =RIGHT(A1,2)/3600/24, and copy-and-paste-special-"value and number formats" to overwrite :xx. In both cases, format the resulting cell with the same Custom format above. But I doubt that that is the general solution you are looking for, since it requires that you be mindful of the original form or that you recognized the incorrect results and handle them individually. Perhaps a better solution for you would be to import the time field in Text format, not General. (You can do this using the Import Wizard.) Then use a macro to convert the forms. If you are interested in the details of the macro, indicate that in a response to this thread. Alternatively, use a formula of the following form in a parallel column of helper cells: =IF(LEN(A1)5, --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) Format those cells with the Custom format above, and copy-and paste-special-"value and number formats" to overwrite the original text cells. Then you can delete the helper cells. Note: The formula above assumes that you do __not__ have data of the form 1:2:3, meaning 1h 2m 3s. Your original posting suggests that the form of that data would be 1:02:03 instead. ----- original message ----- "Literalgar" wrote in message ... I am importing time data, which imports in the hh:mm:ss format (or omitting hh and mm if 0 value) to my eyes, but in the general format, so those entries which could represent a time of day are interpreted as a time of day in the AM, and that which could not be a time of day is interpreted as text. e.g. "4:00" in a cell shows as "4:00 AM" in the formular bar and :40 shows as :40 both places, but is formatted as text. Prior to import, and to the eyes after import in the cells themselves, there are five types of entries: 00:00:00 0:00:00 00:00 0:00 :00 How can I convert these text strings and time values to seconds? Can it be done? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Imported time text to seconds
PS....
I wrote: =IF(LEN(A1)5, --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) More reliable: =IF(ISNUMBER(SEARCH(":*:",A1)), --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) ----- original message ----- "JoeU2004" wrote in message ... "Literalgar" wrote: those entries which could represent a time of day are interpreted as a time of day in the AM ... or PM, depending on the numbers. But whenever you have two colons (xx:xx:xx, x:xx:xx or 0:xx:xx), the data is indeed interpreted as hh:mm:ss. You can see this by formatting the cells with the Custom format "[h]:mm:ss" without quotes. The real problem arises with imported times of the form xx:xx or 0:xx (interpreted as hh:mm) and :xx, which is interpreted as text because it is not a valid time form. There are adjustments that you can make to each form after importing. For xx:xx and 0:xx, you could use copy-and-paste-special-divide 60. And for :xx, you could use a formula of the form =RIGHT(A1,2)/3600/24, and copy-and-paste-special-"value and number formats" to overwrite :xx. In both cases, format the resulting cell with the same Custom format above. But I doubt that that is the general solution you are looking for, since it requires that you be mindful of the original form or that you recognized the incorrect results and handle them individually. Perhaps a better solution for you would be to import the time field in Text format, not General. (You can do this using the Import Wizard.) Then use a macro to convert the forms. If you are interested in the details of the macro, indicate that in a response to this thread. Alternatively, use a formula of the following form in a parallel column of helper cells: =IF(LEN(A1)5, --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) Format those cells with the Custom format above, and copy-and paste-special-"value and number formats" to overwrite the original text cells. Then you can delete the helper cells. Note: The formula above assumes that you do __not__ have data of the form 1:2:3, meaning 1h 2m 3s. Your original posting suggests that the form of that data would be 1:02:03 instead. ----- original message ----- "Literalgar" wrote in message ... I am importing time data, which imports in the hh:mm:ss format (or omitting hh and mm if 0 value) to my eyes, but in the general format, so those entries which could represent a time of day are interpreted as a time of day in the AM, and that which could not be a time of day is interpreted as text. e.g. "4:00" in a cell shows as "4:00 AM" in the formular bar and :40 shows as :40 both places, but is formatted as text. Prior to import, and to the eyes after import in the cells themselves, there are five types of entries: 00:00:00 0:00:00 00:00 0:00 :00 How can I convert these text strings and time values to seconds? Can it be done? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Imported time text to seconds
Do you also post at the Mr. Excel forum?
There's someone there with a similar user name. -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... PS.... I wrote: =IF(LEN(A1)5, --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) More reliable: =IF(ISNUMBER(SEARCH(":*:",A1)), --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) ----- original message ----- "JoeU2004" wrote in message ... "Literalgar" wrote: those entries which could represent a time of day are interpreted as a time of day in the AM ... or PM, depending on the numbers. But whenever you have two colons (xx:xx:xx, x:xx:xx or 0:xx:xx), the data is indeed interpreted as hh:mm:ss. You can see this by formatting the cells with the Custom format "[h]:mm:ss" without quotes. The real problem arises with imported times of the form xx:xx or 0:xx (interpreted as hh:mm) and :xx, which is interpreted as text because it is not a valid time form. There are adjustments that you can make to each form after importing. For xx:xx and 0:xx, you could use copy-and-paste-special-divide 60. And for :xx, you could use a formula of the form =RIGHT(A1,2)/3600/24, and copy-and-paste-special-"value and number formats" to overwrite :xx. In both cases, format the resulting cell with the same Custom format above. But I doubt that that is the general solution you are looking for, since it requires that you be mindful of the original form or that you recognized the incorrect results and handle them individually. Perhaps a better solution for you would be to import the time field in Text format, not General. (You can do this using the Import Wizard.) Then use a macro to convert the forms. If you are interested in the details of the macro, indicate that in a response to this thread. Alternatively, use a formula of the following form in a parallel column of helper cells: =IF(LEN(A1)5, --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) Format those cells with the Custom format above, and copy-and paste-special-"value and number formats" to overwrite the original text cells. Then you can delete the helper cells. Note: The formula above assumes that you do __not__ have data of the form 1:2:3, meaning 1h 2m 3s. Your original posting suggests that the form of that data would be 1:02:03 instead. ----- original message ----- "Literalgar" wrote in message ... I am importing time data, which imports in the hh:mm:ss format (or omitting hh and mm if 0 value) to my eyes, but in the general format, so those entries which could represent a time of day are interpreted as a time of day in the AM, and that which could not be a time of day is interpreted as text. e.g. "4:00" in a cell shows as "4:00 AM" in the formular bar and :40 shows as :40 both places, but is formatted as text. Prior to import, and to the eyes after import in the cells themselves, there are five types of entries: 00:00:00 0:00:00 00:00 0:00 :00 How can I convert these text strings and time values to seconds? Can it be done? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Imported time text to seconds
"T. Valko" wrote:
Do you also post at the Mr. Excel forum? There's someone there with a similar user name. Tried to reply in email, but I got a delivery error for that email address. For personal communication, send email to joeu2004 "at" hotmail.com |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Imported time text to seconds
Hi,
This may give you another idea: Suppose A1 contains the text entry :03 for 3 seconds. Then =--("00:00"&A1) will convert this to 12:00:03 AM. You will need to apply a time format to the cell after entering the formula. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Literalgar" wrote: Thank you JoeU2004. I will work with your suggestions, and let you know (hopefully by the green check!!!!). Many thanks for the time and effort you put into your response. "JoeU2004" wrote: "Literalgar" wrote: those entries which could represent a time of day are interpreted as a time of day in the AM .... or PM, depending on the numbers. But whenever you have two colons (xx:xx:xx, x:xx:xx or 0:xx:xx), the data is indeed interpreted as hh:mm:ss. You can see this by formatting the cells with the Custom format "[h]:mm:ss" without quotes. The real problem arises with imported times of the form xx:xx or 0:xx (interpreted as hh:mm) and :xx, which is interpreted as text because it is not a valid time form. There are adjustments that you can make to each form after importing. For xx:xx and 0:xx, you could use copy-and-paste-special-divide 60. And for :xx, you could use a formula of the form =RIGHT(A1,2)/3600/24, and copy-and-paste-special-"value and number formats" to overwrite :xx. In both cases, format the resulting cell with the same Custom format above. But I doubt that that is the general solution you are looking for, since it requires that you be mindful of the original form or that you recognized the incorrect results and handle them individually. Perhaps a better solution for you would be to import the time field in Text format, not General. (You can do this using the Import Wizard.) Then use a macro to convert the forms. If you are interested in the details of the macro, indicate that in a response to this thread. Alternatively, use a formula of the following form in a parallel column of helper cells: =IF(LEN(A1)5, --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) Format those cells with the Custom format above, and copy-and paste-special-"value and number formats" to overwrite the original text cells. Then you can delete the helper cells. Note: The formula above assumes that you do __not__ have data of the form 1:2:3, meaning 1h 2m 3s. Your original posting suggests that the form of that data would be 1:02:03 instead. ----- original message ----- "Literalgar" wrote in message ... I am importing time data, which imports in the hh:mm:ss format (or omitting hh and mm if 0 value) to my eyes, but in the general format, so those entries which could represent a time of day are interpreted as a time of day in the AM, and that which could not be a time of day is interpreted as text. e.g. "4:00" in a cell shows as "4:00 AM" in the formular bar and :40 shows as :40 both places, but is formatted as text. Prior to import, and to the eyes after import in the cells themselves, there are five types of entries: 00:00:00 0:00:00 00:00 0:00 :00 How can I convert these text strings and time values to seconds? Can it be done? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Imported time text to seconds
"Shane Devenshire" wrote:
This may give you another idea: Suppose A1 contains the text entry :03 for 3 seconds. Then =--("00:00"&A1) Great idea! Putting it all together: =IF(ISNUMBER(SEARCH(":*:",A1)), --A1, IF(LEFT(A1,1)=":", --("00:00"&A1), --("00:"&A1))) ----- original message ----- "Shane Devenshire" wrote in message ... Hi, This may give you another idea: Suppose A1 contains the text entry :03 for 3 seconds. Then =--("00:00"&A1) will convert this to 12:00:03 AM. You will need to apply a time format to the cell after entering the formula. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Literalgar" wrote: Thank you JoeU2004. I will work with your suggestions, and let you know (hopefully by the green check!!!!). Many thanks for the time and effort you put into your response. "JoeU2004" wrote: "Literalgar" wrote: those entries which could represent a time of day are interpreted as a time of day in the AM .... or PM, depending on the numbers. But whenever you have two colons (xx:xx:xx, x:xx:xx or 0:xx:xx), the data is indeed interpreted as hh:mm:ss. You can see this by formatting the cells with the Custom format "[h]:mm:ss" without quotes. The real problem arises with imported times of the form xx:xx or 0:xx (interpreted as hh:mm) and :xx, which is interpreted as text because it is not a valid time form. There are adjustments that you can make to each form after importing. For xx:xx and 0:xx, you could use copy-and-paste-special-divide 60. And for :xx, you could use a formula of the form =RIGHT(A1,2)/3600/24, and copy-and-paste-special-"value and number formats" to overwrite :xx. In both cases, format the resulting cell with the same Custom format above. But I doubt that that is the general solution you are looking for, since it requires that you be mindful of the original form or that you recognized the incorrect results and handle them individually. Perhaps a better solution for you would be to import the time field in Text format, not General. (You can do this using the Import Wizard.) Then use a macro to convert the forms. If you are interested in the details of the macro, indicate that in a response to this thread. Alternatively, use a formula of the following form in a parallel column of helper cells: =IF(LEN(A1)5, --A1, IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24, (LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24)) Format those cells with the Custom format above, and copy-and paste-special-"value and number formats" to overwrite the original text cells. Then you can delete the helper cells. Note: The formula above assumes that you do __not__ have data of the form 1:2:3, meaning 1h 2m 3s. Your original posting suggests that the form of that data would be 1:02:03 instead. ----- original message ----- "Literalgar" wrote in message ... I am importing time data, which imports in the hh:mm:ss format (or omitting hh and mm if 0 value) to my eyes, but in the general format, so those entries which could represent a time of day are interpreted as a time of day in the AM, and that which could not be a time of day is interpreted as text. e.g. "4:00" in a cell shows as "4:00 AM" in the formular bar and :40 shows as :40 both places, but is formatted as text. Prior to import, and to the eyes after import in the cells themselves, there are five types of entries: 00:00:00 0:00:00 00:00 0:00 :00 How can I convert these text strings and time values to seconds? Can it be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seconds into time | Excel Worksheet Functions | |||
How do I change a minutes and seconds as a 2400 time to seconds? | Excel Worksheet Functions | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel | |||
problem working with time data imported from text file | Excel Worksheet Functions |