+ Reply to Thread
Results 1 to 5 of 5

Days in this Quarter

  1. #1
    Registered User
    Join Date
    04-07-2011
    Location
    Lonodn, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Days in this Quarter

    Hi Excel gurus,

    I'm new to the forum, so firstly Hi! I'm looking for a better solution to a problem that I have a somewhat inelegant formula for...

    I'm trying to find out, for an arbitrary date, how many days have gone in the current quarter e.g. today (7th April) would return the result "7" because it's the 7th day of Q2. The solution I fudged together is...

    "=TODAY()-DATE(YEAR(TODAY()),CHOOSE(ROUNDUP(MONTH(TODAY())/3,0),1,4,7,10),1)+1"

    ... which works but is long winded and seems a bit rubbish. Could anyone suggest improvements?

    Many thanks in advance,
    Dave

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Days in this Quarter

    You can decide if it's an improvement:

    =TODAY()-DATE(YEAR(TODAY()),LOOKUP(MONTH(TODAY()),{1,4,7,10},{1,4,7,10}),1)+1

    or

    =TODAY()-DATE(YEAR(TODAY()),CHOOSE(MONTH(TODAY()),1,1,1,4,4,4,7,7,7,10,10,10),1)+1
    which eliminates the ROUNDUP() and division calculations from your original formula
    Last edited by Cutter; 04-12-2011 at 12:36 PM.

  3. #3
    Forum Contributor
    Join Date
    09-23-2008
    Location
    Mexico
    Posts
    200

    Re: Days in this Quarter

    Another option:
    =TODAY()-DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1)

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Days in this Quarter

    One more

    =TODAY()-EDATE(DATE(YEAR(TODAY()),1,1),3*INT((MONTH(TODAY())-1)/3))+1

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Days in this Quarter

    With today's date in A2 try

    =A2-LOOKUP(A2,DATE(YEAR(A2),{1,4,7,10},1))+1
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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