On Mon, 25 Jun 2007 05:21:00 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

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?

It's not the basis of the request, but it is a requirement.

The real point is not to change the underlying data at all -- just
format it for printing. It's like if I put "39258" in a cell. If I
select the "General" format, I will see "39258". If I select the
Number format with 2 decimal places, I will see "39258.00". If I
select the Currency format, I will see "$39,258.00". And.... if I
select the Date format, I'll see "6/25". But no matter what format I
use, the contents of the cell remains "39258".

That's what I want here, except that I want Excel to run my function
and use the value it returns as the display value. I don't want to
change the underlying data at all.

I can't believe that Excel doesn't support this type of custom
formats. t would be trivial to implement.

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.

There are a lot of work-arounds.

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.

"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


--



--