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

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

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

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

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

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

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

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

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