How do I ignore text when calculating time?

[Moonbeam111]

I ranges E41:E49 i have times:

0:20 minutes

0:48 minutes

0:13 minutes

0:33 minutes

0:22 minutes

0:10 minutes

0:00 minutes

0:00 minutes

1:29 minutes

I have a function here that I call on:

Function CalcHrsMins(TotalMinutes As Variant) As Variant
Dim varHours, varMinutes
'calculate the hours
varHours = Int(TotalMinutes / 60)
'calculate the remaining minutes
varMinutes = Format(TotalMinutes - (varHours * 60), "00")
'return the combined hours and minutes
CalcHrsMins = varHours & ":" & varMinutes
End Function

Sub test()
Dim varTotHours As Variant
Dim varHrsMinutes As Variant
Dim time As Range
For Each time In Range("E41:E49")
varTotHours = time
varHrsMinutes = CalcHrsMins(varTotHours)
time = varHrsMinutes
Next time
End Sub

But it doesnt work if I call it while I have the "minutes" text in the range of cells. It just automatically does this:

minutes

minutes

minutes

minutes

minutes

minutes

minutes

minutes

minutes

Id like to keep the text and number in the same cell. Does anyone have the knowledge necessary to know how to fix this? If its possible at all?

 

[6StringJazzer]

Do not keep text and number in the same cell. This is not a good practice for reasons you are discovering. I suggest you remove the text " minutes" from your cells, and leave it as a true time value. Then you can format the cells using custom format

m:ss "minutes"

Then you don't have to make any changes to your code, although I have not tested your code to confirm that it works.

 

[Moonbeam111]

That doesn't appear to work with the code I have. If i put it like this in the custom cell field: h "hours" m "minutes". The numbers remain as 0.

 

[6StringJazzer]

In your first post, are you showing us m:ss or h:mm?

 

[6StringJazzer]

What do you want your code to do? I think it needs to be rewritten.

 

[6StringJazzer]

It appears to take a number of minutes and format it as h:mm in a string. But if you use time values to start with there is no reason to do that. What's the overall problem you are trying to solve?

 

[Moonbeam111]

I have a macro that gives me a certain randomized number of minutes and it automatically adds them to one of the cells in range E41:E49 based off a vlookup on the cell to the left.

Set rfound = Range("D41:D49").Find(What:=Range("H22"))
rfound.Offset(0, 1) = rfound.Offset(0, 1) + Range("A73")

I am trying to format it in the format h "hours" m "minutes". For example if it is "20 minutes" show up as 0 hours, 20 minutes. If the cell was 90 minutes, show up as "1 hour 30 minutes". I can do that using the special custom format. h "hours" m "minutes", however if I run the code like that all of the cells in that range change to "0 hours 0 minutes."

I might be getting the problem because the code is trying to add the numbers and text together. And whenever I click the cell it shows up as 12:00:00 AM in the formula bar.

 

[6StringJazzer]

I don't see where you are getting a randomized number of minutes--is that's what's in A73? What is in H22? What is in D41:D49?

If you have time values in E41:E49 as I suggest, and A73 is an integer number of minutes, then you don't need the code you posted in #1 at all. You can modify the code above to this:

Set rfound = Range("D41:D49").Find(What:=Range("H22"))
rfound.Offset(0, 1) = rfound.Offset(0, 1) + Range("A73")/24/60