# Days in this Quarter

1. ## 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?

Dave

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

3. ## Re: Days in this Quarter

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

4. ## Re: Days in this Quarter

One more

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

5. ## Re: Days in this Quarter

With today's date in A2 try

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

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

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