| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert to minutes
Good Morning
Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert to minutes
Is your data text, or numbers?
If numbers, simply multiply each cell by 24*60 =A2*24*60 If text, the easiest way would probably be to do a Data - Text to Columns (space as delimiter), and then do: =A2*24+C2 -- Best Regards, Luke M "da" wrote in message ... Good Morning Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert to minutes
Try
=SUM(HOUR(A1)*60)+MINUTE(A1) -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "da" wrote: Good Morning Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert to minutes
This formula is based on the contents of cells being exactly as you've shown
them, most critically, having a single space before AND after the "HR" or "Hr" or "hr" portion, and on having a single space before the "min" portion. Both HR and MIN can be upper or lower case or a mix of both. This would be to work with a value in cell A2: =IF(ISERR(SEARCH("HR",A2)),IF(ISERR(SEARCH("min",A 2)),0,LEFT(A2,SEARCH("min",A2)-1)*1),LEFT(A2,SEARCH("hr",A2)-1)*60+IF(ISERR(SEARCH("min",A2)),0,MID(A2,SEARCH(" hr",A2)+2,SEARCH("min",A2)-SEARCH("hr",A2)-2))) The system here will no doubt split that formula into several lines, remember that when you enter it into your worksheet, it should be one long, continuous entry. "da" wrote: Good Morning Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert to minutes
Missed something
I'm assuming your input is in following format 0:00 -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "da" wrote: Good Morning Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert to minutes
Thank you.
However, all times are shown in the following format. How do I conver it to all minutes? thanks 7:20 PM 10:40 PM 4:00 PM 6:30 PM 7:45 PM 8:45 PM 10:25 PM 12:00 AM 5:30 PM 6:30 PM 6:20 PM 9:40 PM 8:30 PM 9:30 PM 6:30 PM 9:40 PM 9:00 PM 11:15 PM "JLatham" wrote: This formula is based on the contents of cells being exactly as you've shown them, most critically, having a single space before AND after the "HR" or "Hr" or "hr" portion, and on having a single space before the "min" portion. Both HR and MIN can be upper or lower case or a mix of both. This would be to work with a value in cell A2: =IF(ISERR(SEARCH("HR",A2)),IF(ISERR(SEARCH("min",A 2)),0,LEFT(A2,SEARCH("min",A2)-1)*1),LEFT(A2,SEARCH("hr",A2)-1)*60+IF(ISERR(SEARCH("min",A2)),0,MID(A2,SEARCH(" hr",A2)+2,SEARCH("min",A2)-SEARCH("hr",A2)-2))) The system here will no doubt split that formula into several lines, remember that when you enter it into your worksheet, it should be one long, continuous entry. "da" wrote: Good Morning Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert to minutes
Sorry to mislead you. However times are shown as following:
3:50 PM 4:25 PM 5:30 PM 6:30 PM 4:10 PM 6:40 PM 6:40 PM 8:45 PM 6:55 PM 8:50 PM 3:15 PM 5:45 PM 4:05 PM 5:15 PM "Russell Dawson" wrote: Missed something I'm assuming your input is in following format 0:00 -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "da" wrote: Good Morning Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert to minutes
Format the cells
Custom hh:mm Then use formula -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "da" wrote: Sorry to mislead you. However times are shown as following: 3:50 PM 4:25 PM 5:30 PM 6:30 PM 4:10 PM 6:40 PM 6:40 PM 8:45 PM 6:55 PM 8:50 PM 3:15 PM 5:45 PM 4:05 PM 5:15 PM "Russell Dawson" wrote: Missed something I'm assuming your input is in following format 0:00 -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "da" wrote: Good Morning Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert to minutes
Thank you all for your help.
I will try all suggestions. "Luke M" wrote: Is your data text, or numbers? If numbers, simply multiply each cell by 24*60 =A2*24*60 If text, the easiest way would probably be to do a Data - Text to Columns (space as delimiter), and then do: =A2*24+C2 -- Best Regards, Luke M "da" wrote in message ... Good Morning Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert to minutes
I tried. In the future, please provide accurate representation of your data
in its format so that a correct answer can be provided without wasting anyone's time chasing a rabbit down a dark tunnel. One of the other solutions should actually do what you need with what you have. "da" wrote: Thank you. However, all times are shown in the following format. How do I conver it to all minutes? thanks 7:20 PM 10:40 PM 4:00 PM 6:30 PM 7:45 PM 8:45 PM 10:25 PM 12:00 AM 5:30 PM 6:30 PM 6:20 PM 9:40 PM 8:30 PM 9:30 PM 6:30 PM 9:40 PM 9:00 PM 11:15 PM "JLatham" wrote: This formula is based on the contents of cells being exactly as you've shown them, most critically, having a single space before AND after the "HR" or "Hr" or "hr" portion, and on having a single space before the "min" portion. Both HR and MIN can be upper or lower case or a mix of both. This would be to work with a value in cell A2: =IF(ISERR(SEARCH("HR",A2)),IF(ISERR(SEARCH("min",A 2)),0,LEFT(A2,SEARCH("min",A2)-1)*1),LEFT(A2,SEARCH("hr",A2)-1)*60+IF(ISERR(SEARCH("min",A2)),0,MID(A2,SEARCH(" hr",A2)+2,SEARCH("min",A2)-SEARCH("hr",A2)-2))) The system here will no doubt split that formula into several lines, remember that when you enter it into your worksheet, it should be one long, continuous entry. "da" wrote: Good Morning Is there any way I can convert following to minutes using a formula or any other way? Thank you 35 Min 1 HR 2 Hr 30 min 2 Hr 5 min 2 Hr 5 min 2 Hr 30 min 1 Hr 10 min 2 Hr 2 Hr 15 min 3 Hr 5 min 1 HR |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I have 3053 minutes and I want to convert to hours and minutes | Excel Discussion (Misc queries) | |||
| Convert total minutes to hours and minutes | Excel Discussion (Misc queries) | |||
| Convert Decimal hours and Minutes to minutes please. | Excel Worksheet Functions | |||
| How do I convert a number of minutes into hours and minutes? | Excel Worksheet Functions | |||
| Convert a number of minutes like 400 into hours and minutes | Excel Worksheet Functions | |||
Linear Mode
