Convert text of 2 days, 19 hours, 56 minutes to numeric value?

idkwts

New Member
Joined
Apr 28, 2011
Messages
3
Hi
I have a report that generates time data in this format in an excel cell:

2 days, 19 hours, 56 minutes

How can I convert this to a numeric value so that I can use the data for calculations?

Any help is greatly appreciated!
Thanks!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Welcome to the forums!

Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">2 days, 19 hours, 56 minutes</td><td style="text-align: right;;">2.83</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=VALUE(<font color="Blue">LEFT(<font color="Red">A1,2</font>)</font>)+TIME(<font color="Blue">MID(<font color="Red">A1,FIND(<font color="Green">"hours",A1</font>)-3,2</font>),MID(<font color="Red">A1,FIND(<font color="Green">"minutes",A1</font>)-3,2</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Hi
I have a report that generates time data in this format in an excel cell:

2 days, 19 hours, 56 minutes

How can I convert this to a numeric value so that I can use the data for calculations?

Any help is greatly appreciated!
Thanks!
Here's another one...

Assuming the days can be any number, the hours will not be greater than 23 and the minutes will not be greater than 59.

=LEFT(A2,FIND(" ",A2))+TIMEVALUE(MID(A2,FIND(",",A2)+1,3)&":"&LEFT(RIGHT(A2,10),3))
 
Upvote 0
Welcome to the forums!

Try:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>2 days, 19 hours, 56 minutes</TD><TD style="TEXT-ALIGN: right">2.83</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B1</TH><TD style="TEXT-ALIGN: left">=VALUE(LEFT(A1,2))+TIME(MID(A1,FIND("hours",A1)-3,2),MID(A1,FIND("minutes",A1)-3,2),0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
That doesn't account for days greater than 99, if that's a possibility.
 
Upvote 0
That doesn't account for days greater than 99, if that's a possibility.

Good point. In that case, an altered version of mine would be:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">2 days, 19 hours, 56 minutes</td><td style="text-align: right;;">2.83</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=VALUE(<font color="Blue">LEFT(<font color="Red">A1,FIND(<font color="Green">" ",A1</font>)</font>)</font>)+TIME(<font color="Blue">MID(<font color="Red">A1,FIND(<font color="Green">"hours",A1</font>)-3,2</font>),MID(<font color="Red">A1,FIND(<font color="Green">"minutes",A1</font>)-3,2</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />)
 
Upvote 0
Thanks so much!

Just one caveat. Not all the data has days, hours and minutes. For example, it may be:

<TABLE style="WIDTH: 135pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=180><COLGROUP><COL style="WIDTH: 135pt; mso-width-source: userset; mso-width-alt: 6582" width=180><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 135pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" id=td_post_2699291 class=xl68 height=20 width=180>20 hours, 58 minutes</TD></TR></TBODY></TABLE> -or-
<TABLE style="WIDTH: 135pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=180><COLGROUP><COL style="WIDTH: 135pt; mso-width-source: userset; mso-width-alt: 6582" width=180><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 135pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" id=td_post_2699291 class=xl68 height=20 width=180>37 minutes</TD></TR></TBODY></TABLE>

Any way to have it work if not all variables are present?

It works great if the text contains all 3 though! :)
 
Upvote 0
What version of Excel do you use? That will greatly affect what formula I provide.
 
Upvote 0
Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">2 days, 19 hours, 56 minutes</td><td style="text-align: right;;">2.830556</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">19 hours, 56 minutes</td><td style="text-align: right;;">0.830556</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">56 minutes</td><td style="text-align: right;;">0.038889</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">19 hours</td><td style="text-align: right;;">0.791667</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">2 days</td><td style="text-align: right;;">2.000000</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">2 days, 19 hours</td><td style="text-align: right;;">2.791667</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">2 days, 56 minutes</td><td style="text-align: right;;">2.038889</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">56 minutes, 2 days, 19 hours</td><td style="text-align: right;;">2.830556</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IFERROR(<font color="Blue">VALUE(<font color="Red">MID(<font color="Green">" "&A1,SEARCH(<font color="Purple">"days"," "&A1</font>)-3,2</font>)</font>),0</font>)+IFERROR(<font color="Blue">TIME(<font color="Red">VALUE(<font color="Green">MID(<font color="Purple">" "&A1,SEARCH(<font color="Teal">"hours"," "&A1</font>)-3,2</font>)</font>),0,0</font>),0</font>)+IFERROR(<font color="Blue">TIME(<font color="Red">0,VALUE(<font color="Green">MID(<font color="Purple">" "&A1,SEARCH(<font color="Teal">"minutes"," "&A1</font>)-3,2</font>)</font>),0</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top