The problem is in showing the units as part of the result. Excel sees
something like "30.0M" in a cell and doesn't think it's numeric.
You can kind of get around that in a cell formula by using this to get rid
of the units indicator (assumes 30.0M in A1, and formula in B1, or any other
cell)
=Value(Left(A1,Len(A1)-1)
which will show just 30 in the cell with the formula.

Your next problem is to figure out what the resulting value is! Is 30 now
referring to Seconds, Minutes, Hours, Days or Years. You may be best off
writing yet another user defined function to take the displayed entry in such
a cell and convert it to whatever units you may need for any math operations
to be performed. It might look something like the code below, and would be
called (using example with the 30.0M in A1 above) as =ReverseFmtInt(A1)

But I see a loss of accuracy in going back and forth with these functions.
An entry of 370 results in FmtInt returning 1.0Y, but a conversion using
=ReverseFmtInt("1.0Y") would return 365.25 meaning that 4 and 3/4 days got
lost in translation.

Public Function ReverseFmtInt(someTime As String) As Double
Const TSYear As Double = 365.25 ' 1 year
Const TSDay As Double = 1 ' 1 day
Const TSHour As Double = TSDay / 24 ' 1 hour
Const TSMin As Double = TSHour / 60 ' 1 minute
Const TSSec As Double = TSMin / 60 ' 1 second

Dim timeValue As Double

If Len(someTime) 1 Then
timeValue = Val(Left(someTime, Len(someTime) - 1))

Select Case Right(someTime, 1)
Case "Y"
ReverseFmtInt = timeValue * TSYear
Case "D"
ReverseFmtInt = timeValue * TSDay
Case "H"
ReverseFmtInt = timeValue * TSHour
Case "M"
ReverseFmtInt = timeValue * TSMin
Case "S"
ReverseFmtInt = timeValue * TSSec
Case Else
ReverseFmtInt = 0 ' can't parse input
End Select
Else
ReverseFmtInt = 0 ' can't parse input
End If
End Function

"LurfysMa" wrote:

In case anyone is interested, here's the formatting macro:

'************************************************* ***********************
' Format Interval Function

' Formats an interval as nnn.nu

' nnn.n = interval to 1 decimal place
' u = units: Y=years, D=days, H=hours, M=minutes, S=seconds

' Syntax: y = FmtInt(interval)

Public Function FmtInt(ByVal interval As Double) As String

Const TSYear As Double = 365.25 ' 1 year
Const TSDay As Double = 1 ' 1 day
Const TSHour As Double = TSDay / 24 ' 1 hour
Const TSMin As Double = TSHour / 60 ' 1 minute
Const TSSec As Double = TSMin / 60 ' 1 second

Dim result As String 'Intermediate value
Dim units As String 'Units (see above)

If interval = TSYear Then
result = interval / TSYear
units = "Y"
ElseIf interval = TSDay Then
result = interval
units = "D"
ElseIf interval = TSHour Then
result = interval / TSHour
units = "H"
ElseIf interval = TSMin Then
result = interval / TSMin
units = "M"
Else
result = interval / TSSec
units = "S"
End If

FmtInt = Format(result, "0.0") & units

End Function


--