Days left in a quarter

[jswillcox]

I am trying to finda a formula that will show how many days are left in a quarter.

I have one to find the days into a quarter you are
=TODAY()-LOOKUP(TODAY(),DATE(YEAR(TODAY()),{1,4,7,10},1))+1


I would like a formula to show the number of days left in the quarter including the current day.

For example:
19 Feb 2016 we have 42 days left in the quarter.
19 Feb 2017 we have 41 days left in the quarter.

Any assistance you can give would be appreciated.

 

[MarcelBeug]

This will do the trick:

=EOMONTH(TODAY(),2-MOD(MONTH(TODAY())+2,3))-TODAY()+1

 

[jswillcox]

Thank you! That takes care of it!

 

[Jeffrey Mahoney]

I know these are long formulas and maybe somebody else can come up with a cleaner version, but here's my attempt.

This one is for the current date:
=CHOOSE(MONTH(NOW()),DAYS360(NOW()-1,DATE(YEAR(NOW()),4,1)),DAYS360(NOW()-1,DATE(YEAR(NOW()),4,1)),DAYS360(NOW()-1,DATE(YEAR(NOW()),4,1)),DAYS360(NOW()-1,DATE(YEAR(NOW()),7,1)),DAYS360(NOW()-1,DATE(YEAR(NOW()),7,1)),DAYS360(NOW()-1,DATE(YEAR(NOW()),7,1)),DAYS360(NOW()-1,DATE(YEAR(NOW())+1,1,1)),DAYS360(NOW()-1,DATE(YEAR(NOW()),10,1)),DAYS360(NOW()-1,DATE(YEAR(NOW()),10,1)),DAYS360(NOW()-1,DATE(YEAR(NOW())+1,1,1)),DAYS360(NOW()-1,DATE(YEAR(NOW())+1,1,1)),DAYS360(NOW()-1,DATE(YEAR(NOW())+1,1,1)))

This one is for any date in a Cell (C4 in this example):
=CHOOSE(MONTH(C4),DAYS360(C4-1,DATE(YEAR(C4),4,1)),DAYS360(C4-1,DATE(YEAR(C4),4,1)),DAYS360(C4-1,DATE(YEAR(C4),4,1)),DAYS360(C4-1,DATE(YEAR(C4),7,1)),DAYS360(C4-1,DATE(YEAR(C4),7,1)),DAYS360(C4-1,DATE(YEAR(C4),7,1)),DAYS360(C4-1,DATE(YEAR(C4)+1,1,1)),DAYS360(C4-1,DATE(YEAR(C4),10,1)),DAYS360(C4-1,DATE(YEAR(C4),10,1)),DAYS360(C4-1,DATE(YEAR(C4)+1,1,1)),DAYS360(C4-1,DATE(YEAR(C4)+1,1,1)),DAYS360(C4-1,DATE(YEAR(C4)+1,1,1)))

Jeff