Formatting text to be used in calculations 1

[marshybid]

Hi All,

I have a set of data that is delivered to me with a number of the cells containing the folloowing information;

0 HRS 18 MIN (example of one cell)
5 DAYS 4 HRS 3 MIN (example of another cell)

These cells are formatted as general.

I need to be able to use the days, hours and minutes values as part of a formula, but I don't know how to remove the DAYS, HRS, MIN references and just use the values. I do need the values to be seen as Days, Hrs and Min in the formula. Help gratefully received.

Thanks,

Marshybid

 

[SkipVought]

Hi,

First off, I'd contact person who generates to source to determine, 1) if the output can be modified to identifiable numeric values or 2) if they can't, what their source is.

Short of that, I assume that you have ONLY DAYS, HRS and MIN.

Function DatTimVal(r)
    Dim a, i As Integer, sTest As String
    a = Split(r.Value, " ")
    For i = 0 To UBound(a)
        Select Case a(i)
            Case "DAYS"
                DatTimVal = DatTimVal + a(i - 1)
            Case "HRS"
                DatTimVal = DatTimVal + a(i - 1) / 24
            Case "MIN"
                DatTimVal = DatTimVal + a(i - 1) / 24 / 60
            Case Else
            
        End Select
    Next
End Function

Copy and paste into a MODULE.

Use as you would any spreadsheet function, supplying the cell range as the argument.

Skip,

_{Just got a nuance...
to replace the old subtlety!}

 

[marshybid]

Thanks Skip,

Sorry I'm new to VBA.

I have a piece of code that I use to iterate through my worksheet to remove unwanted data.

Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet
Set myBaseRange = myBaseWorkSheet.Rows
For RowsCounter = myBaseRange.Rows.Count To 2 Step -1
    Set myBaseRow = myBaseRange.Item(RowsCounter)
    If Len(myBaseRow.Cells.Item(1, 8)) <> 0 Then
    
       '// Delete data that we do not need for each tab
        If myBaseRow.Cells.Item(1, 21) <> "Approved" Then
        
        
            myBaseRow.Delete
            
        End If
    End If
Next

I assume I would use something similar to provide the range for your function to work through the sheet?? Is that correct.

marshybid (naively)

 

[SkipVought]

dim rng as range
for each rng in Range([A1], [A1].end(xldown))
   rng.value = DatTimVal(rng)
next

Skip,

_{Just got a nuance...
to replace the old subtlety!}