extract time from text import

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

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>

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

 

[Guest]

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>

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

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>

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

 

[Guest]

Thanks, I'll give it a try and let you know how I make out.


Pete

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>

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

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>


:

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

 

[Guest]

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

Thanks, I'll give it a try and let you know how I make out.


Pete

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>

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

:

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>


:

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

 

[Guest]

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

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

Thanks, I'll give it a try and let you know how I make out.


Pete

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>


:

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

:

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>


:

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