Convert Text in cell to hh:mm:ss
 

One of the columns in my spreadsheet has a text field displaying 1 days 1 hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

Hi,

the easy way: select the cell and choose Data, Text to Columns, Delimited,
Next, check Space, Next, in the preview pane select days column and then
click do not import skip, repeat this for the hrs, min, and sec column, pick
a destination cell and click Finish. Suppose the destination cell was B1, in
A2 enter

=B1*1440+C1*60+D1+E1/60
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"dgarza" wrote:

One of the columns in my spreadsheet has a text field displaying 1 days 1 hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

Hi,

The hard way takes more work, if the entry is in A1 then

=1440*LEFT(A1,FIND("
",A1)-1)+60*MID(A1,FIND("days",A1)+4,3)+MID(A1,FIND("hrs ",A1)+4,2)+MID(A1,FIND("min",A1)+4,2)/60

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"dgarza" wrote:

One of the columns in my spreadsheet has a text field displaying 1 days 1 hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

Is that "text" really text? Or is it an Excel date/time value *formatted* to
look like you showed us?

--
Rick (MVP - Excel)


"dgarza" wrote in message
...
One of the columns in my spreadsheet has a text field displaying 1 days 1
hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

I'm having kind of the same problem. I am using a data dump out of a 3rd
party system, and it is placing "4:14:51:11" into a cell and formatted as
General. I am trying to run a calculation off of it and it won't work. My
end goal is to convert to seconds. I would prefer not to go through the
hassle/effort of deliniating the 4 things into Days, Hours, Minutes and
Seconds then doing the calculation.

Thank you for your attention to this,
Greg

"Rick Rothstein" wrote:

Is that "text" really text? Or is it an Excel date/time value *formatted* to
look like you showed us?

--
Rick (MVP - Excel)


"dgarza" wrote in message
...
One of the columns in my spreadsheet has a text field displaying 1 days 1
hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

The problem is d:h:m:s is not a valid time in Excel (the days part screws it
up). You can use this function call directly in your calculations and it
will do the conversion of that format to seconds (substitute your local's
abbreviated name for January where I have Jan in my function call)...

TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

If you use this in a calculation, it will Excel will convert it to a number
in order to perform that calculation. If you just want to put the generated
value in a cell, you will need to prod Excel into performing a calculation.
I like to use the double-unary (--) to do this...

=--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

although you could just multiply by one if that seems clearer to you....

=1*TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

--
Rick (MVP - Excel)


"Greg" wrote in message
...
I'm having kind of the same problem. I am using a data dump out of a 3rd
party system, and it is placing "4:14:51:11" into a cell and formatted as
General. I am trying to run a calculation off of it and it won't work.
My
end goal is to convert to seconds. I would prefer not to go through the
hassle/effort of deliniating the 4 things into Days, Hours, Minutes and
Seconds then doing the calculation.

Thank you for your attention to this,
Greg

"Rick Rothstein" wrote:

Is that "text" really text? Or is it an Excel date/time value *formatted*
to
look like you showed us?

--
Rick (MVP - Excel)


"dgarza" wrote in message
...
One of the columns in my spreadsheet has a text field displaying 1 days
1
hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

Amazing! That's just GREAT. Thank you very much for the prompt and very
accurate reply.
Sincerely,
Greg

"Rick Rothstein" wrote:

The problem is d:h:m:s is not a valid time in Excel (the days part screws it
up). You can use this function call directly in your calculations and it
will do the conversion of that format to seconds (substitute your local's
abbreviated name for January where I have Jan in my function call)...

TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

If you use this in a calculation, it will Excel will convert it to a number
in order to perform that calculation. If you just want to put the generated
value in a cell, you will need to prod Excel into performing a calculation.
I like to use the double-unary (--) to do this...

=--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

although you could just multiply by one if that seems clearer to you....

=1*TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

--
Rick (MVP - Excel)


"Greg" wrote in message
...
I'm having kind of the same problem. I am using a data dump out of a 3rd
party system, and it is placing "4:14:51:11" into a cell and formatted as
General. I am trying to run a calculation off of it and it won't work.
My
end goal is to convert to seconds. I would prefer not to go through the
hassle/effort of deliniating the 4 things into Days, Hours, Minutes and
Seconds then doing the calculation.

Thank you for your attention to this,
Greg

"Rick Rothstein" wrote:

Is that "text" really text? Or is it an Excel date/time value *formatted*
to
look like you showed us?

--
Rick (MVP - Excel)


"dgarza" wrote in message
...
One of the columns in my spreadsheet has a text field displaying 1 days
1
hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

Rick, again, I appreciate your help.

This has now brought up a new issue, and I've looked through the forum and
don't know where to post, so I thought I'd keep it on this topic.

Some of my cells are d:hh:mm:ss and some are just hh:mm:ss.

Here's my question, is there a way to say
IF(HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11) = Error (namely #VALUE!
because the format is d:hh:mm:ss) then --TEXT(SUBSTITUTE(A1,":","Jan1900
",1), "[s]"), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

"Rick Rothstein" wrote:

The problem is d:h:m:s is not a valid time in Excel (the days part screws it
up). You can use this function call directly in your calculations and it
will do the conversion of that format to seconds (substitute your local's
abbreviated name for January where I have Jan in my function call)...

TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

If you use this in a calculation, it will Excel will convert it to a number
in order to perform that calculation. If you just want to put the generated
value in a cell, you will need to prod Excel into performing a calculation.
I like to use the double-unary (--) to do this...

=--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

although you could just multiply by one if that seems clearer to you....

=1*TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

--
Rick (MVP - Excel)


"Greg" wrote in message
...
I'm having kind of the same problem. I am using a data dump out of a 3rd
party system, and it is placing "4:14:51:11" into a cell and formatted as
General. I am trying to run a calculation off of it and it won't work.
My
end goal is to convert to seconds. I would prefer not to go through the
hassle/effort of deliniating the 4 things into Days, Hours, Minutes and
Seconds then doing the calculation.

Thank you for your attention to this,
Greg

"Rick Rothstein" wrote:

Is that "text" really text? Or is it an Excel date/time value *formatted*
to
look like you showed us?

--
Rick (MVP - Excel)


"dgarza" wrote in message
...
One of the columns in my spreadsheet has a text field displaying 1 days
1
hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

The formula I gave you should work for both d:hh:mm:ss and hh:mm:ss... there
should be no errors reported for it; well, except if the cell is empty. In
that case, use this...

=IF(A1="","",--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]"))

--
Rick (MVP - Excel)


"Greg" wrote in message
...
Rick, again, I appreciate your help.

This has now brought up a new issue, and I've looked through the forum and
don't know where to post, so I thought I'd keep it on this topic.

Some of my cells are d:hh:mm:ss and some are just hh:mm:ss.

Here's my question, is there a way to say
IF(HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11) = Error (namely #VALUE!
because the format is d:hh:mm:ss) then --TEXT(SUBSTITUTE(A1,":","Jan1900
",1), "[s]"), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

"Rick Rothstein" wrote:

The problem is d:h:m:s is not a valid time in Excel (the days part screws
it
up). You can use this function call directly in your calculations and it
will do the conversion of that format to seconds (substitute your local's
abbreviated name for January where I have Jan in my function call)...

TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

If you use this in a calculation, it will Excel will convert it to a
number
in order to perform that calculation. If you just want to put the
generated
value in a cell, you will need to prod Excel into performing a
calculation.
I like to use the double-unary (--) to do this...

=--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

although you could just multiply by one if that seems clearer to you....

=1*TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

--
Rick (MVP - Excel)


"Greg" wrote in message
...
I'm having kind of the same problem. I am using a data dump out of a
3rd
party system, and it is placing "4:14:51:11" into a cell and formatted
as
General. I am trying to run a calculation off of it and it won't work.
My
end goal is to convert to seconds. I would prefer not to go through
the
hassle/effort of deliniating the 4 things into Days, Hours, Minutes and
Seconds then doing the calculation.

Thank you for your attention to this,
Greg

"Rick Rothstein" wrote:

Is that "text" really text? Or is it an Excel date/time value
*formatted*
to
look like you showed us?

--
Rick (MVP - Excel)


"dgarza" wrote in message
...
One of the columns in my spreadsheet has a text field displaying 1
days
1
hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

Here's a few of the results so you can understand more of what I am having to
deal with so hopefully it can help our little discussion:

7:03:06:08 = 615,968 seconds = Correct Result
00:27:29 = #VALUE! = Problem
22:33:43 = 2,022,180 = Incorrect Result

All calcs done with:

=--TEXT(SUBSTITUTE(B21,":","Jan1900 ",1), "[s]")

Thank you again for your efforts with this,
Greg

"Rick Rothstein" wrote:

The formula I gave you should work for both d:hh:mm:ss and hh:mm:ss... there
should be no errors reported for it; well, except if the cell is empty. In
that case, use this...

=IF(A1="","",--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]"))

--
Rick (MVP - Excel)


"Greg" wrote in message
...
Rick, again, I appreciate your help.

This has now brought up a new issue, and I've looked through the forum and
don't know where to post, so I thought I'd keep it on this topic.

Some of my cells are d:hh:mm:ss and some are just hh:mm:ss.

Here's my question, is there a way to say
IF(HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11) = Error (namely #VALUE!
because the format is d:hh:mm:ss) then --TEXT(SUBSTITUTE(A1,":","Jan1900
",1), "[s]"), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

"Rick Rothstein" wrote:

The problem is d:h:m:s is not a valid time in Excel (the days part screws
it
up). You can use this function call directly in your calculations and it
will do the conversion of that format to seconds (substitute your local's
abbreviated name for January where I have Jan in my function call)...

TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

If you use this in a calculation, it will Excel will convert it to a
number
in order to perform that calculation. If you just want to put the
generated
value in a cell, you will need to prod Excel into performing a
calculation.
I like to use the double-unary (--) to do this...

=--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

although you could just multiply by one if that seems clearer to you....

=1*TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

--
Rick (MVP - Excel)


"Greg" wrote in message
...
I'm having kind of the same problem. I am using a data dump out of a
3rd
party system, and it is placing "4:14:51:11" into a cell and formatted
as
General. I am trying to run a calculation off of it and it won't work.
My
end goal is to convert to seconds. I would prefer not to go through
the
hassle/effort of deliniating the 4 things into Days, Hours, Minutes and
Seconds then doing the calculation.

Thank you for your attention to this,
Greg

"Rick Rothstein" wrote:

Is that "text" really text? Or is it an Excel date/time value
*formatted*
to
look like you showed us?

--
Rick (MVP - Excel)


"dgarza" wrote in message
...
One of the columns in my spreadsheet has a text field displaying 1
days
1
hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

Ah, your cells are formatted as Text. Okay, try this formula...

=--TEXT(SUBSTITUTE(IF(ISERR(--A1),A1,--A1),":","Jan1900 ",1), "[s]")

--
Rick (MVP - Excel)


"Greg" wrote in message
...
Here's a few of the results so you can understand more of what I am having
to
deal with so hopefully it can help our little discussion:

7:03:06:08 = 615,968 seconds = Correct Result
00:27:29 = #VALUE! = Problem
22:33:43 = 2,022,180 = Incorrect Result

All calcs done with:

=--TEXT(SUBSTITUTE(B21,":","Jan1900 ",1), "[s]")

Thank you again for your efforts with this,
Greg

"Rick Rothstein" wrote:

The formula I gave you should work for both d:hh:mm:ss and hh:mm:ss...
there
should be no errors reported for it; well, except if the cell is empty.
In
that case, use this...

=IF(A1="","",--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]"))

--
Rick (MVP - Excel)


"Greg" wrote in message
...
Rick, again, I appreciate your help.

This has now brought up a new issue, and I've looked through the forum
and
don't know where to post, so I thought I'd keep it on this topic.

Some of my cells are d:hh:mm:ss and some are just hh:mm:ss.

Here's my question, is there a way to say
IF(HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11) = Error (namely #VALUE!
because the format is d:hh:mm:ss)
then --TEXT(SUBSTITUTE(A1,":","Jan1900
",1), "[s]"), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

"Rick Rothstein" wrote:

The problem is d:h:m:s is not a valid time in Excel (the days part
screws
it
up). You can use this function call directly in your calculations and
it
will do the conversion of that format to seconds (substitute your
local's
abbreviated name for January where I have Jan in my function call)...

TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

If you use this in a calculation, it will Excel will convert it to a
number
in order to perform that calculation. If you just want to put the
generated
value in a cell, you will need to prod Excel into performing a
calculation.
I like to use the double-unary (--) to do this...

=--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

although you could just multiply by one if that seems clearer to
you....

=1*TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

--
Rick (MVP - Excel)


"Greg" wrote in message
...
I'm having kind of the same problem. I am using a data dump out of
a
3rd
party system, and it is placing "4:14:51:11" into a cell and
formatted
as
General. I am trying to run a calculation off of it and it won't
work.
My
end goal is to convert to seconds. I would prefer not to go through
the
hassle/effort of deliniating the 4 things into Days, Hours, Minutes
and
Seconds then doing the calculation.

Thank you for your attention to this,
Greg

"Rick Rothstein" wrote:

Is that "text" really text? Or is it an Excel date/time value
*formatted*
to
look like you showed us?

--
Rick (MVP - Excel)


"dgarza" wrote in message
...
One of the columns in my spreadsheet has a text field displaying
1
days
1
hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

Wow! I almost feel bad getting your advice for free.

Seriously, Thank you very much for your help with this and posting it on the
forum. It's greatly appreciated.
Greg

"Rick Rothstein" wrote:

Ah, your cells are formatted as Text. Okay, try this formula...

=--TEXT(SUBSTITUTE(IF(ISERR(--A1),A1,--A1),":","Jan1900 ",1), "[s]")

--
Rick (MVP - Excel)


"Greg" wrote in message
...
Here's a few of the results so you can understand more of what I am having
to
deal with so hopefully it can help our little discussion:

7:03:06:08 = 615,968 seconds = Correct Result
00:27:29 = #VALUE! = Problem
22:33:43 = 2,022,180 = Incorrect Result

All calcs done with:

=--TEXT(SUBSTITUTE(B21,":","Jan1900 ",1), "[s]")

Thank you again for your efforts with this,
Greg

"Rick Rothstein" wrote:

The formula I gave you should work for both d:hh:mm:ss and hh:mm:ss...
there
should be no errors reported for it; well, except if the cell is empty.
In
that case, use this...

=IF(A1="","",--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]"))

--
Rick (MVP - Excel)


"Greg" wrote in message
...
Rick, again, I appreciate your help.

This has now brought up a new issue, and I've looked through the forum
and
don't know where to post, so I thought I'd keep it on this topic.

Some of my cells are d:hh:mm:ss and some are just hh:mm:ss.

Here's my question, is there a way to say
IF(HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11) = Error (namely #VALUE!
because the format is d:hh:mm:ss)
then --TEXT(SUBSTITUTE(A1,":","Jan1900
",1), "[s]"), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

"Rick Rothstein" wrote:

The problem is d:h:m:s is not a valid time in Excel (the days part
screws
it
up). You can use this function call directly in your calculations and
it
will do the conversion of that format to seconds (substitute your
local's
abbreviated name for January where I have Jan in my function call)...

TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

If you use this in a calculation, it will Excel will convert it to a
number
in order to perform that calculation. If you just want to put the
generated
value in a cell, you will need to prod Excel into performing a
calculation.
I like to use the double-unary (--) to do this...

=--TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

although you could just multiply by one if that seems clearer to
you....

=1*TEXT(SUBSTITUTE(A1,":","Jan1900 ",1), "[s]")

--
Rick (MVP - Excel)


"Greg" wrote in message
...
I'm having kind of the same problem. I am using a data dump out of
a
3rd
party system, and it is placing "4:14:51:11" into a cell and
formatted
as
General. I am trying to run a calculation off of it and it won't
work.
My
end goal is to convert to seconds. I would prefer not to go through
the
hassle/effort of deliniating the 4 things into Days, Hours, Minutes
and
Seconds then doing the calculation.

Thank you for your attention to this,
Greg

"Rick Rothstein" wrote:

Is that "text" really text? Or is it an Excel date/time value
*formatted*
to
look like you showed us?

--
Rick (MVP - Excel)


"dgarza" wrote in message
...
One of the columns in my spreadsheet has a text field displaying
1
days
1
hrs
30 min 18 sec and I would like to convert this to total minutes.

Thank you

David

Convert Text in cell to hh:mm:ss
 

Wow! I almost feel bad getting your advice for free.

You can pay me if that will make you feel better.<g

ONLY KIDDING!

Seriously, Thank you very much for your help with this and posting
it on the forum. It's greatly appreciated.

You are quite welcome; it was my pleasure.

--
Rick (MVP - Excel)