VBA calculating minutes

[christian2016]

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

 

[mrshl9898]

How's this?

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]

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]

How's this?

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]

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):

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

 

[christian2016]

joeu2004 thank you.

The formula is working well