VBA calculating minutes

christian2016

Board Regular
Joined
Oct 6, 2016
Messages
105
Hi Guys,

I have data in column V.

Data range will vary with data starting under the header in V2.

Format of each cell is the same format example.
1 days, 1 hours, 4 minutes and 0 seconds

All that changes are the values.

I need a VBA to convert the format into minutes and this formula will need to be applied down column W starting at W2.

Any help is greatly appreciated.

Thanks
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,631
How's this?

Code:
Sub findhours()

rownum = 2


Do Until Cells(rownum, 22).Value = ""


Cells(rownum, 23).FormulaR1C1 = "=NUMBERVALUE(MID(RC[-1],FIND(""minutes"",RC[-1])-3,2))"
rownum = rownum + 1
Loop


End Sub
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,974
Office Version
  1. 2010
Platform
  1. Windows
Format of each cell is the same format example.
1 days, 1 hours, 4 minutes and 0 seconds

That is not a "format".

Do you mean that the cell values are text with the literal words "days", "hours", "minutes and", "seconds" separated by commas as shown?

Or are the cell values some numeric with a numeric format that might be something like Custom 0" days, "0" hours, "0" minutes and "0" seconds"?

Well, that seems unlikely. But since you ask for VBA code, perhaps you are doing something with the numeric format in VBA(?).

If time is less than a day, do you have "0 days"? Or do you start with the first non-zero unit?

I have data in column V.
Data range will vary with data starting under the header in V2.
Format of each cell is the same format example.
1 days, 1 hours, 4 minutes and 0 seconds
[....]
I need a VBA to convert the format into minutes and this formula will need to be applied down column W starting at W2.

If it can be done efficiently with an Excel formula, would be happier with that instead of VBA?

Do you want a VBA function that you can call line-by-line? In that case, we do not need to know it would "be applied down the column".

Or do you want a VBA macro/subroutine or something added to existing code that you must invoke manually?

If the latter, instead of knowing about V2 and W2, the more-user-friendly design might be to highlight the two starting cells instead of hardcoding V2 and W2 in the subroutine. Is that acceptable?

Finally, when you say "convert into minutes", do you mean decimal minutes (1 days, 2 hours, 3 minutes and 4 seconds would be about 1563.06666666667.)? Or do you mean Excel time formatted as minutes (my example would be 1563 or 1563:04)? And do you want minutes truncated, round or what (specify)?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,631
How's this?

Code:
Sub findhours()

rownum = 2


Do Until Cells(rownum, 22).Value = ""


Cells(rownum, 23).FormulaR1C1 = "=NUMBERVALUE(MID(RC[-1],FIND(""minutes"",RC[-1])-3,2))"
rownum = rownum + 1
Loop


End Sub

Sorry OP, my post was extracting minutes. Please ignore.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,974
Office Version
  1. 2010
Platform
  1. Windows
Do you mean that the cell values are text with the literal words "days", "hours", "minutes and", "seconds" separated by commas as shown?
[....]
If time is less than a day, do you have "0 days"?
[....]
If it can be done efficiently with an Excel formula, would be happier with that instead of VBA?
Do you want a [....] function that you can call line-by-line?
[....]
Finally, when you say "convert into minutes", do you mean decimal minutes (1 days, 2 hours, 3 minutes and 4 seconds would be about 1563.06666666667.)?

Presuming "yes" to all of my questions above, perhaps the following works for you (entered into W2 and copied down the column):

Code:
=1440 * (LEFT(V2,FIND(" ",V2))
+ SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(V2,7+FIND(" days, ",V2),99)," hours, ",":")," minutes and ",":")," seconds",""))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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
Top