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~

Place this in cell C1

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

HTH

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

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

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

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
You got your date.
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

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))))

'Need more information on the problem with cell B8.