+ Reply to Thread
Results 1 to 3 of 3

Subtracting MONTH Function from another MONTH Function not working for Fiscal dates

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Subtracting MONTH Function from another MONTH Function not working for Fiscal dates

    Hello,

    I have a formula for a Calendar calculation that takes (MONTH($AB9)-MONTH($AA9)) and gives me the number of months. AB and AA would have dates in them. 1/1/2012 - 6/30/2012 etc. When I save the file over for Fiscal calculations the formula breaks because my fiscal date is 7/1/2013 - 6/30/2014. I end up getting a negative 1. Can anyone help me figure out a formula so that regardless if it is a calendar or fiscal file it will calculate the number of months between the two dates resulting in a positive number? The negative number is messing with my count. I can't use an ABS in the formula because 6/30/2014 minus 7/1/2013 would still give me 1 and I need it to give me 12.

    Any help is much appreciated.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Subtracting MONTH Function from another MONTH Function not working for Fiscal dates

    Try using DATEDIF, e.g.

    =DATEDIF(AA9,AB9+1,"m")
    Audere est facere

  3. #3
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Subtracting MONTH Function from another MONTH Function not working for Fiscal dates

    works like a charm. thanks daddylonglegs.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month
    By bimmer5dude in forum Excel General
    Replies: 5
    Last Post: 06-30-2012, 09:13 PM
  2. [SOLVED] Pivot: Replace month numbers with month names and sort by fiscal year
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2011, 02:13 PM
  3. Function Fiscal Year and Month
    By Bob@Sun in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2010, 01:07 PM
  4. Replies: 1
    Last Post: 08-03-2006, 08:25 AM
  5. [SOLVED] Autofill between dates in Fiscal Month
    By akid12 in forum Excel General
    Replies: 3
    Last Post: 06-13-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1