Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date math (Excel 2003)

    In <post#=406650>post 406650</post#> I got a way to get a fiscal quarter from a date. I am now trying to simply get the Fiscal year end date so that any date from 4/1/05 to 3/31/06 would = 3/31/06 and 4/1/06-3/31/07 = 3/31/07, or 2006 and 2007 would be fine. Can't seem to get date math down. I'll keep looking but any help would be appreciated. TYIA!

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date math (Excel 2003)

    Assuming your date is in A1:

    =IF(A1>DATE(YEAR(A1),3,31),DATE(IF(MONTH(A1)<=3,YE AR(A1),YEAR(A1)+1),3,31),DATE(YEAR(A1),3,31))

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date math (Excel 2003)

    Or slightly simpler
    <code>
    =DATE(YEAR(A1)+(MONTH(A1)>3),3,31)
    </code>
    This uses the fact that FALSE = 0 and TRUE = 1. If MONTH(A1) is greater than 3 (i.e. April through December), MONTH(A1)>3 evaluates to TRUE=1, and this is added to the year to obtain a date in the next year.

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date math (Excel 2003)

    Thanks! Both work well and I'll add them to my library. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Posting Permissions

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