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

I have been thinking more about this and I suspect that the whole basis of
your request is to get away from that loss of precision that I mentioned
earlier.  Correct?

One way to do that would be to use two cells to display the result of your
function: have it place the numeric result into the cell with the formula in
it, formatted to display as 0.0, and then put the type time indicator into a
cell one column over on the same row.  I don't know if this is an acceptable
solution or not.

You'd need to modify your function FmtInt() to accept 3 parameters:
Function FmtInt(byVal interval as Double, anyRow as Long, anyColumn as Long)
as Double

You'd call it from a sheet as
= FmtInt(X5,Row(),Column())
while the X5 would be used same as you do now, the Row(),Column() parameters
are always entered in that fashion.  That way they always provide the row and
column values of the cell with the formula(s) in it(them).

Later your code would end with

 Cells(anyRow, anyColumn+1) = units ' puts YDHMS into next column
 Cells(anyRow, anyColumn).NumberFormat = "0.0"
 FmtInt = result
End Function

I haven't tested this yet, but seems to me it should work.