=QuarterNum(): How to make it work in Excel 2003

[Clueless in Seattle]

I recently switched from Excel 97 to Excel 2003

Today I tried to use Excel 2003 to enter data into a worksheet I
originally created with Word 97.

One of the columns in the original worksheet contains the formula
=QuarterNum(cell reference)

Under Excel 97 this formula would display the quarter number for a date
entered into the referenced cell.

But under Excel 2003 it displays ####.

When I place the cursor over the cell a tan colored box pops up
displaying #NAME? And when I click on the cell a white box containing
an exclmation point inside a diamond shape pops up to the left of the
cell.

I seem to recall having to go monkey around with Excel 97 to get the
=QuarterNum() formula to work, but I'm afraid my memory doesn't server
me very well these days and I'm not able to remember how I got it to
work. I searched this newsgroup for "Quarter Number" and "QuarterNum"
but got no hits.

I'd be grateful to someone who would be willing to help me out with
this.

Will in Seattle
a.k.a. "Clueless"

 

[Pete_UK]

I'm not familiar with this function - I suspect it might be a
User-defined function that you may have set up in your personal.xls
file for Excel 97. Do you still have Excel 97 on your PC that you can
check this out with?

Pete

 

[dougaj4]

This link:

http://www.exceltip.com/st/Adding_a_Custom_Function_That_Returns_the_Quarter_Number/92.html

describes how to create a quarternum custom function.

Very easy, one line of code!

 

[Clueless in Seattle]

Hi Pete!

Thanks for offering to help!

A friend recently gave me his old computer, which is the one I have
Excel 2003 installed on. I have this "new" computer connected in a
peer-to-peer network with my old computer, the one with Excel 97
installed on it.

I don't have a monitor connected to the old computer, so I'm not able
to run the old software. But I'm able to access all the files on the
old computer. Will that be of any use?

I have a spare monitor, keyboard and mouse, in storage that I could
dig out and hook up to the old computer and then I would be able to run
the old installation of Excel 97.

If I do that, then what would I look for? I'm afraid I'll need rather
simple minded instructions; on the level of "first click this, then
look for that, then click this, etc." because I suffer from cognitive
impairment from a circulation disorder (my brain doesn't get adequate
blood flow when I sit up at the computer).

Will in Seattle
a.k.a. "Clueless"

 

[Clueless in Seattle]

This is very strange.

I followed the instructions from that link you provided.

And when VBE opened, I found that the QuarterNum function already
exists. Are the functions stored in the spreadsheet files rather than
in the program files?

In any event, the function seems to be already in place. So the
question now seems to be: Why doesn't it work?

Here's what it looks like, cut and pasted from my Excel 2003
spreadsheet Visual Basic window:

Function QuarterNum(Enter_Date)
QuarterNum = DatePart("q", Enter_Date)
End Function

Will in Seattle
a.k.a. "Clueless"

 

[dougaj4]

And when VBE opened, I found that the QuarterNum function already
exists. Are the functions stored in the spreadsheet files rather than
in the program files?

The built-in functions are stored in the program files. "Custom" (or
user defined) functions are stored in the spreadsheet file in which
they are created, unless they are converted to an add-in.

At some time in the past someone must have added the quarternum()
function to the spreadsheet you are using.

In any event, the function seems to be already in place. So the
question now seems to be: Why doesn't it work?

I have no idea at the moment

Here's what it looks like, cut and pasted from my Excel 2003
spreadsheet Visual Basic window:

Function QuarterNum(Enter_Date)
QuarterNum = DatePart("q", Enter_Date)
End Function

That code works for me (Excel 2003)

Try this, from the Insert-function dialog box, select "user defined"
from the category drop-down list.

Does quarternum() appear?

If so, try it from there.

 

[Clueless in Seattle]

Try this, from the Insert-function dialog box, select "user defined"
from the category drop-down list.

Does quarternum() appear?

Not exactly.

Here's what comes up in the "Select a function" window:

'FileName.xls'!Module1.QuarterNum

When I click the "OK" button the dialogue box is replaced by a box
labled "Function Arguments"

And when I click "OK" in that box, the function is entered into the
cell, as above, but it displays "#NAME? instead of the expected quarter
number.

 

[Clueless in Seattle]

Here's something that may be germane:

Each time I used Exdel 2003 to open the Excel file containing the
QuarterNum function created with Excel 97, I get this message:

"Macros are disabled because the security level is set to High
and a digitally signed Trusted Certificat is not attached to the
macros. To run the macros, change the security level to a
lower setting (not recommended), or request the macros be
signed by the author using a certificate issued by a Certifi-
cate authority.

I've ignored that warning, because it seemed to me that we are dealing
here with "functions" not "macros."

But could this warning be a clue as to what the problem might be?

Will in Seattle
a.k.a. "Clueless"

P.S. In the meantime, I'm going to try deleting and then recreating
the QuarterNum function (If I can figure out how to do that. Night
before last I suffered a seizure-like episode that has left my memory
and concentration in a sorry state).

 

[Franz Verga]

Nel post *Clueless in Seattle* ha scritto:

Here's something that may be germane:

Each time I used Exdel 2003 to open the Excel file containing the
QuarterNum function created with Excel 97, I get this message:

"Macros are disabled because the security level is set to High
and a digitally signed Trusted Certificat is not attached to the
macros. To run the macros, change the security level to a
lower setting (not recommended), or request the macros be
signed by the author using a certificate issued by a Certifi-
cate authority.

I've ignored that warning, because it seemed to me that we are dealing
here with "functions" not "macros."

But could this warning be a clue as to what the problem might be?

Yes. This *is* your problem.

Also User Definied Functions are macro...

So from Tools, Macros, Security you have to set to Medium your security
level. In this way every time you'll open a file, not only this one with
QuarterNum UDF function, that have inside some VBA code (macros or
functions...), you'll be prompted to choose to run or not to run the macros:
if you choose to not run them, the file could not work properly, asis the
case of your QuarterNum UDF.

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Bobbie]

Will:
I found this in Microsoft 2000 Formulas:
=ROUNDUP(MONTH(celladress)/3,0)

I use it in my spreadseets and it works very well, if you are looking
for a simple function to determine a months quarter.

Bobbie

 

[Clueless in Seattle]

Molti ringraziamenti, Franz!

That did the trick!

And many thanks to all the others who pitched in to help me with this
problem.

I've learned (or rather, relearned) a great deal in the process.

I'm grateful to all of you for being so patient with me and my failing
memory.

Will in Seattle
a.k.a. "Clueless"