Hi Lea,

This is a super advanced formula that uses concepts typically only known and understood by the developers of Excel, namely broadcasting and pairwise. Here is my explanation of the last part of the formula (note: my dates are dd/mm/yyyy):

CEILING(MONTH(B4),3) - Finds the last month in the quarter. e.g. for 1st Feb 2018 it finds 3 as the last month in the qtr.

SUM(DATE(YEAR(B4),CEILING(MONTH(B4),3)+{-2,1},0) - The +{-2,1} generates two values for the Month argument in the DATE function. i.e. Where B4 = 1st Feb 2018 CEILING(MONTH(B4),3)+{-2,1} returns 1 i.e. (3-2) and 4 i.e. (3+1).

SUM(DATE(YEAR(B4),{1,4},0) - The DATE function therefore returns two dates; 43100 and 43190 (which are 31/12/2017 and 31/3/2019) This is because the zero in the day argument of DATE returns the date prior to the first date of the month in the month argument. i.e. DATE(2018,1,0) returns 31/12/2017 and DATE(2018,4,0) returns 31/3/2018

SUM({43100,43190}*{-1,1}) -*{-1,1} converts the first date to a negative and the second date to a positive so that the SUM function subtracts one from the other. It returns:

SUM({-43100,43190})

Which results in the number of days in the quarter i.e. 90

For an understanding on pairwise and broadcasting I encourage you to watch this video by Joe McDaid from the Excel team at Microsoft where he explains how arrays work in Excel.

Mynda

Hey dear Minda
I thank you very much for the detailed answer and patience to answer me, I was very curious to understand the structure of the function.
Thanks!! Very appreciative !! Leah