Convert Text in cell to hh:mm:ss

[dgarza]

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

 

[Shane Devenshire]

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

 

[Shane Devenshire]

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

 

[Rick Rothstein]

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

 

[Greg]

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]

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), "")

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), "")

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

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

 

[Greg]

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

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), "")

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), "")

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

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

--
Rick (MVP - Excel)

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

 

[Greg]

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), ""), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

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), "")

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), "")

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

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

--
Rick (MVP - Excel)

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]

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), ""))

--
Rick (MVP - Excel)

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), ""), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

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), "")

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), "")

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

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

--
Rick (MVP - Excel)

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

:

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

--
Rick (MVP - Excel)


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

 

[Greg]

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), "")

Thank you again for your efforts with this,
Greg

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), ""))

--
Rick (MVP - Excel)

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), ""), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

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), "")

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), "")

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

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

--
Rick (MVP - Excel)


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

:

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

--
Rick (MVP - Excel)


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

 

[Rick Rothstein]

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

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

--
Rick (MVP - Excel)

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), "")

Thank you again for your efforts with this,
Greg

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), ""))

--
Rick (MVP - Excel)

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), ""), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

:

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), "")

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), "")

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

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

--
Rick (MVP - Excel)


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

:

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

--
Rick (MVP - Excel)


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

 

[Greg]

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

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

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

--
Rick (MVP - Excel)

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), "")

Thank you again for your efforts with this,
Greg

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), ""))

--
Rick (MVP - Excel)


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), ""), otherwise (HOUR(B11)*3600)+(MINUTE(B11)*60)+SECOND(B11)

I hope that makes sense.

Thank you,
Greg

:

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), "")

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), "")

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

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

--
Rick (MVP - Excel)


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

:

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

--
Rick (MVP - Excel)


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

 

[Rick Rothstein]

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.