Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract time from text import
When I import a text selection, the date format gets changed from 1316:10 to
1316:10:00. It looks like Excel is trying to interpret the time format to a date and time format. I've tried using the LEFT function to extract only the 1316, but excel has already converted the time to its time and date format. A watch is added to the VBA code to see how excel is intrepretting it. 1316:10 gets converted to 165 or 2/23/1900 8:10:00 PM. The question is, how do I extract only the hh:mm or hh:mm:ss? Thanks in advance, Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract time from text import
Hi,
Try: =TEXT(B2,"[h]") Your cell is formatted as [h]:mm:ss and not h:mm:ss. The difference is that while the second one is a point in time (a date), the first one is an interval or a length of time.... or something like that. -- Regards, Sébastien <http://www.ondemandanalysis.com "HmxPete" wrote: When I import a text selection, the date format gets changed from 1316:10 to 1316:10:00. It looks like Excel is trying to interpret the time format to a date and time format. I've tried using the LEFT function to extract only the 1316, but excel has already converted the time to its time and date format. A watch is added to the VBA code to see how excel is intrepretting it. 1316:10 gets converted to 165 or 2/23/1900 8:10:00 PM. The question is, how do I extract only the hh:mm or hh:mm:ss? Thanks in advance, Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract time from text import
Pete,
Text is a worksheet-function, so you have to use it either as: - within the worksheet in a cell: =TEXT(A1,"[h]") - or within vba as application.WorksheetFunction.Text(activecell,"[h]") Does this work for you? Regards, Sébastien <http://www.ondemandanalysis.com "HmxPete" wrote: Thanks for replying so quickly, sorry for my delay. I tried using the following visual basic statement to extract the time; BreakTime = Text(BreakTime, "[h]") But the pop-up window appears: Compile error: Sub or Function not defined Is "text" a new command in current versions of excel? Is there a similar command for older versions, oh... say ... like excel97? Thanks again, Pete "sebastienm" wrote: Hi, Try: =TEXT(B2,"[h]") Your cell is formatted as [h]:mm:ss and not h:mm:ss. The difference is that while the second one is a point in time (a date), the first one is an interval or a length of time.... or something like that. -- Regards, Sébastien <http://www.ondemandanalysis.com "HmxPete" wrote: When I import a text selection, the date format gets changed from 1316:10 to 1316:10:00. It looks like Excel is trying to interpret the time format to a date and time format. I've tried using the LEFT function to extract only the 1316, but excel has already converted the time to its time and date format. A watch is added to the VBA code to see how excel is intrepretting it. 1316:10 gets converted to 165 or 2/23/1900 8:10:00 PM. The question is, how do I extract only the hh:mm or hh:mm:ss? Thanks in advance, Pete |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract time from text import
Thanks, I'll give it a try and let you know how I make out.
Pete "sebastienm" wrote: Pete, Text is a worksheet-function, so you have to use it either as: - within the worksheet in a cell: =TEXT(A1,"[h]") - or within vba as application.WorksheetFunction.Text(activecell,"[h]") Does this work for you? Regards, Sébastien <http://www.ondemandanalysis.com "HmxPete" wrote: Thanks for replying so quickly, sorry for my delay. I tried using the following visual basic statement to extract the time; BreakTime = Text(BreakTime, "[h]") But the pop-up window appears: Compile error: Sub or Function not defined Is "text" a new command in current versions of excel? Is there a similar command for older versions, oh... say ... like excel97? Thanks again, Pete "sebastienm" wrote: Hi, Try: =TEXT(B2,"[h]") Your cell is formatted as [h]:mm:ss and not h:mm:ss. The difference is that while the second one is a point in time (a date), the first one is an interval or a length of time.... or something like that. -- Regards, Sébastien <http://www.ondemandanalysis.com "HmxPete" wrote: When I import a text selection, the date format gets changed from 1316:10 to 1316:10:00. It looks like Excel is trying to interpret the time format to a date and time format. I've tried using the LEFT function to extract only the 1316, but excel has already converted the time to its time and date format. A watch is added to the VBA code to see how excel is intrepretting it. 1316:10 gets converted to 165 or 2/23/1900 8:10:00 PM. The question is, how do I extract only the hh:mm or hh:mm:ss? Thanks in advance, Pete |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract time from text import
Hi Sebastien,
It works great, I'm using the two lines of code to import the text into excel; BreakTime = Application.WorksheetFunction.Text(ActiveCell, "[h]") BreakTime = Format(BreakTime, "##:##") The only problem is when the BreakTime is between 00:00 and 00:59. The programming code used does not covert the text "0053:40". Other breaks after 01:00 convert perfectly. Any ideas? Thanks for your help, we're almost there. Pete "HmxPete" wrote: Thanks, I'll give it a try and let you know how I make out. Pete "sebastienm" wrote: Pete, Text is a worksheet-function, so you have to use it either as: - within the worksheet in a cell: =TEXT(A1,"[h]") - or within vba as application.WorksheetFunction.Text(activecell,"[h]") Does this work for you? Regards, Sébastien <http://www.ondemandanalysis.com "HmxPete" wrote: Thanks for replying so quickly, sorry for my delay. I tried using the following visual basic statement to extract the time; BreakTime = Text(BreakTime, "[h]") But the pop-up window appears: Compile error: Sub or Function not defined Is "text" a new command in current versions of excel? Is there a similar command for older versions, oh... say ... like excel97? Thanks again, Pete "sebastienm" wrote: Hi, Try: =TEXT(B2,"[h]") Your cell is formatted as [h]:mm:ss and not h:mm:ss. The difference is that while the second one is a point in time (a date), the first one is an interval or a length of time.... or something like that. -- Regards, Sébastien <http://www.ondemandanalysis.com "HmxPete" wrote: When I import a text selection, the date format gets changed from 1316:10 to 1316:10:00. It looks like Excel is trying to interpret the time format to a date and time format. I've tried using the LEFT function to extract only the 1316, but excel has already converted the time to its time and date format. A watch is added to the VBA code to see how excel is intrepretting it. 1316:10 gets converted to 165 or 2/23/1900 8:10:00 PM. The question is, how do I extract only the hh:mm or hh:mm:ss? Thanks in advance, Pete |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract time from text import
Hi Sebastien,
Just wanted to let you know I found a work around to get desired results; BreakTime = Application.WorksheetFunction.Text(ActiveCell, "[h]") Dim tmpBreakTime As String If InStr(BreakTime, ":") 0 Then tmpBreakTime = Left(BreakTime, InStr(BreakTime, ":") - 1) Dim tmp1 As String Dim tmp2 As String tmp1 = Left(tmpBreakTime, 2) tmp2 = Right(tmpBreakTime, 2) tmpBreakTime = tmp1 & ":" & tmp2 BreakTime = tmpBreakTime Else BreakTime = Format(BreakTime, "##:##") End If Thanks for all your help. Pete "HmxPete" wrote: Hi Sebastien, It works great, I'm using the two lines of code to import the text into excel; BreakTime = Application.WorksheetFunction.Text(ActiveCell, "[h]") BreakTime = Format(BreakTime, "##:##") The only problem is when the BreakTime is between 00:00 and 00:59. The programming code used does not covert the text "0053:40". Other breaks after 01:00 convert perfectly. Any ideas? Thanks for your help, we're almost there. Pete "HmxPete" wrote: Thanks, I'll give it a try and let you know how I make out. Pete "sebastienm" wrote: Pete, Text is a worksheet-function, so you have to use it either as: - within the worksheet in a cell: =TEXT(A1,"[h]") - or within vba as application.WorksheetFunction.Text(activecell,"[h]") Does this work for you? Regards, Sébastien <http://www.ondemandanalysis.com "HmxPete" wrote: Thanks for replying so quickly, sorry for my delay. I tried using the following visual basic statement to extract the time; BreakTime = Text(BreakTime, "[h]") But the pop-up window appears: Compile error: Sub or Function not defined Is "text" a new command in current versions of excel? Is there a similar command for older versions, oh... say ... like excel97? Thanks again, Pete "sebastienm" wrote: Hi, Try: =TEXT(B2,"[h]") Your cell is formatted as [h]:mm:ss and not h:mm:ss. The difference is that while the second one is a point in time (a date), the first one is an interval or a length of time.... or something like that. -- Regards, Sébastien <http://www.ondemandanalysis.com "HmxPete" wrote: When I import a text selection, the date format gets changed from 1316:10 to 1316:10:00. It looks like Excel is trying to interpret the time format to a date and time format. I've tried using the LEFT function to extract only the 1316, but excel has already converted the time to its time and date format. A watch is added to the VBA code to see how excel is intrepretting it. 1316:10 gets converted to 165 or 2/23/1900 8:10:00 PM. The question is, how do I extract only the hh:mm or hh:mm:ss? Thanks in advance, Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refer a cell text in b1 and extract remaing text from a1 | Excel Discussion (Misc queries) | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
How do I extract time only in Excel? | Excel Discussion (Misc queries) | |||
How to extract time and date | Excel Discussion (Misc queries) | |||
Add leading 0 to csv import text/time H:MM | Excel Discussion (Misc queries) |