# Thread: using a function result in a cell formula

1. Registered User
Join Date
Oct 2003
Posts
311

## Unanswered: using a function result in a cell formula

I would like to have cell c1 = cell b1 - 3 months, (b1 is a date 6/30/04) how can i create a function in vba and ref that function in the cell formula. some thing like :

pub func priorQtr as date

dim m as int = cint(range(b1).value).month -3
dim d as int = cdate(m & "/01/" & now.year).daysinmonth

return cdate(m & "/" & d & "/" & now.year)

end func

As u can c im not used to vba so any help with getting my date would also b aprreciated

M~

2. Registered User
Join Date
May 2004
Posts
6
Place this in cell C1

=DATE(YEAR(B1), MONTH(B1) - 3, DAY(B1))

HTH

3. Registered User
Join Date
Oct 2003
Posts
311
2 problems

1 - if q2 ends 6/30/04 the result is 3/30/04 not 3/31 which it should be.

2 - my cell b8 formula is : =RIGHT(A3,8) and that doenst work with the Date function

4. Registered User
Join Date
Mar 2004
Location
Minnesota, USA, Earth
Posts
65

## Either nest a bunch of IF statements or...

If the problem is getting the last day of the month for each end of the quarter, I'd guess that you would need to nest a bunch of If Statements.

In VBA a more workable solution would be to use SELECT CASE.

-MIke

5. Registered User
Join Date
Oct 2003
Posts
311
yes about the case, but i would like to put a formula in the cell that would return the results of my function.

Any one ever do that?

I dont want to have to keep running a macro, im a lazy FSB

~M

6. Registered User
Join Date
Mar 2004
Location
Greenville, SC
Posts
271
I just tried this, it worked perfectly!
Type: =EOMONTH(B1,-3) in cell c1
Then type: =DATE(YEAR(C1), MONTH(C1), DAY(C1)) in cell D1
Also if you get a NAME? Error when you try to use EOMONTH, you may
need to go to:
TOOLS then ADDINS then Analysis Tool Pak to enter older functions!

Hope this helps!

Michael

7. Registered User
Join Date
Mar 2004
Location
Fort Worth, Texas, USA
Posts
68
Play with this to see if it's what you want. Beware of strange things such as 5/30/2004 (not an end of a month) calculating to 2/29/2004 (an end of a month).

=DATE(YEAR(EOMONTH(B4,-3)),MONTH(EOMONTH(B4,-3)),MIN(IF(B4=EOMONTH(B4,0),32,DAY(B4)),DAY(EOMONT H(B4,-3))))