New User-Defined Function in Excel 2000?

[OKCraiger]

I'm trying to create a new user-defined function in Excel 2000 as follows:

A cell will contain a time as hours and minutes, but the minutes are entered
as the decimal fraction, e.g. 7.30 represents 7 hours and 30 minutes.

I then need to create a UDF that converts the value to hours and fractions
of hours, e.g. 7.30 would convert to 7.50 (which 7 hours 30 minutes IS 7.5
hours).

Also ... I know there is an INT function, but is there a function that will
return just the fractional part (e.g. .3)?

Thanks!

Craig

 

[Myrna Larson]

With worksheet functions, =MOD(A1,1) will return the fractional portion. So
will =A1-INT(A1)

But you don't need VBA for this. Worksheet functions, plus one in the Analysis
Tool Pak (ATP) are sufficient.

Assuming you just want to start with 7.3 and end up with 7.5:

=INT(A1)+(A1-INT(A1)))*100/60
or
=DOLLARDE(A1,60) returns 7.5

The 2nd function requires the ATP. It will give a #NAME! error if you don't
have it installed.

 

[Ryan Poth]

How about x - Int(x)?

So your complete formula (in VBA) would be something like

TimeFrac = Int(x) + (x - Int(x)) * 100 / 60

HTH,
Ryan

 

[OKCraiger]

How do I get the ATP? I don't have the DOLLARDE function. Thanks.

 

[Myrna Larson]

Look at Help for the DOLLARDE function. It will tell you what to do and how to
do it.

 

[Gord Dibben]

Analysis Toolpak is an add-in that should have installed with Office and
Excel.

Do you see it when you go to Tools>Add-ins? If not, do a file search for
these files........make sure Windows is set to "show hidden files".

Look for them in your Office10 Library first.

C:\Program Files\Microsoft Office\Office10\Library\Analysis

Four files in here.......

ANALYS32.XLL
ATPVBAEN.XLA
FUNCRES.XLA
PROCDB.XLA

When you do the install do you select "typical" or "custom"? The typical
install will include the toolpak add-in.

If you cannot find anywhere on Hard Drive suggest you plop in the Office CD
and do a partial install through Control Panel>Add/Remove>Change. Perhaps
uninstall the add-in components then re-install.

Gord Dibben Excel MVP