Results 1 to 7 of 7
  1. #1
    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. #2
    Join Date
    May 2004
    Posts
    6
    Place this in cell C1

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

    HTH

  3. #3
    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. #4
    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. #5
    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. #6
    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
    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
    Gotta to do some code

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

    'Need more information on the problem with cell B8.
    Last edited by actuary; 05-16-04 at 13:01.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •