Explanation

In this example, the goal is to generate a list of quarter start and quarter end dates. This can be done by combining the SEQUENCE function with the EDATE and EOMONTH functions, as explained below.

The SEQUENCE function

The SEQUENCE function generates a list of sequential numbers in an array. For example, the formula below generates a sequence of 5 numbers that begin with 1 and end with 5:

=SEQUENCE(5) // returns {1;2;3;4;5}

SEQUENCE has optional arguments for the start value and the step value. For example, if we provide zero as the start value, we get an array of 5 numbers that begin with zero and end with 4:

=SEQUENCE(5,,0) // returns {0;1;2;3;4}

If we provide 3 for the step argument, we get an array of numbers that begin with zero and end with 12:

=SEQUENCE(5,,0,3) // returns {0;3;6;9;12}

We use this behavior in the formulas below.

By default, SEQUENCE will generate data in rows . To generate data in columns , use the optional second argument, columns , and leave rows empty. For a brief overview, watch this video: The SEQUENCE function .

Quarter start dates

To create a list of quarter start dates, we use the EDATE function , which is designed to add months to a given start date. For example, if we use “1-Jan-2024” as the start date, we can create four quarter start dates by providing 0, 3, 6, and 9 to EDATE as the months argument:

=EDATE("1-Jan-2024",0) // returns 1-Jan-2024
=EDATE("1-Jan-2024",3) // returns 1-Apr-2024
=EDATE("1-Jan-2024",6) // returns 1-Jul-2024
=EDATE("1-Jan-2024",9) // returns 1-Oct-2024

The first formula does not change the date since the number of months is zero. The second formula adds 3 months, the third adds 6 months, and the fourth adds 9 months to the date. In all cases, EDATE returns the first of the month since the start date is also the first. In the worksheet shown, our goal is to generate a list of 12 quarter start dates beginning on January 1, 2024 (in cell B5). We do this by combining the EDATE function with the SEQUENCE function in a formula like this:

=EDATE(B5,SEQUENCE(12,,0,3))

Working from the inside out, the SEQUENCE function is configured to create a sequence of 12 numbers, starting at zero and incrementing by 3:

SEQUENCE(12,,0,3)
  • rows - provided as 12 since we want 12 dates at the end
  • columns - empty (defaults to 1)
  • start - given as zero because we want to use begin on the start date
  • step - given as 3 because we want to increment each number by 3

In this configuration, SEQUENCE returns an array of 12 numbers like this:

{0;3;6;9;12;15;18;21;24;27;30;33}

This array is passed directly to the EDATE function as the months argument.

=EDATE(B5,{0;3;6;9;12;15;18;21;24;27;30;33})

EDATE then returns 12 dates that begin with 1-Jan-2024 in an array like this:

{45292;45383;45474;45566;45658;45748;45839;45931;46023;46113;46204;46296}

The array is delivered to cell D5 and spills into the range D5:D16. These are Excel dates in their raw serial number format . Once the range is formatted with the number format “d-mmm-yyyy”, the numbers will display the quarter start dates seen in the worksheet:

{"1-Jan-2024";"1-Apr-2024";"1-Jul-2024";"1-Oct-2024";"1-Jan-2025";"1-Apr-2025";"1-Jul-2025";"1-Oct-2025";"1-Jan-2026";"1-Apr-2026";"1-Jul-2026";"1-Oct-2026"}

Quarter end dates

To create a list of quarter-end dates, we use the EOMONTH function . EOMONTH works just like the EDATE function. However, instead of returning a date on the same day of the month , EOMONTH returns the last day of the month . For example, if we use “1-Jan-2024” as the start date, we can create four quarter-end dates by providing 2, 5, 8, and 11 to EOMONTH as the months argument:

=EOMONTH("1-Jan-2024",2) // returns 31-Mar-2024
=EOMONTH("1-Jan-2024",5) // returns 30-Jun-2024
=EOMONTH("1-Jan-2024",8) // returns 30-Sep-2024
=EOMONTH("1-Jan-2024",11) // returns 31-Dec-2024

Notice that EOMONTH returns the last day of the month even though the start date is the first day of the month . In the worksheet shown, we want to list 12 quarter-end dates based on a start date of January 1, 2024 (in cell B5). We do this with EOMONTH and SEQUENCE like this:

=EOMONTH(B5,SEQUENCE(12,,2,3))

This formula is very similar to the EDATE + SEQUENCE formula we used to create quarter start dates above. The difference is that (1) we use the EOMONTH function instead of EDATE, and (2) we configure SEQUENCE in a slightly different way, starting the sequence at 2 instead of zero:

SEQUENCE(12,,2,3)
  • rows - given as 12 because we want 12 dates
  • columns - empty (defaults to 1)
  • start - given as 2 to add 2 months to the start date
  • step - given as 3 because we want to increment each date by 3 months

In this configuration, SEQUENCE returns an array of 12 numbers like this:

{2;5;8;11;14;17;20;23;26;29;32;35}

This array is returned directly to the EOMONTH function as the months argument:

=EOMONTH(B5,{2;5;8;11;14;17;20;23;26;29;32;35})

EOMONTH then returns 12 dates that begin with 31-Mar-2024 in an array like this:

{45382;45473;45565;45657;45747;45838;45930;46022;46112;46203;46295;46387}

The array is delivered to cell E5 and spills into the range E5:E16. When the number format “d-mmm-yyyy” is applied, the numbers will display the quarter-end dates seen in the worksheet:

{"31-Mar-2024";"30-Jun-2024";"30-Sep-2024";"31-Dec-2024";"31-Mar-2025";"30-Jun-2025";"30-Sep-2025";"31-Dec-2025";"31-Mar-2026";"30-Jun-2026";"30-Sep-2026";"31-Dec-2026"}

In the worksheet, the formula looks like this:

Listing quarter end dates with the EOMONTH function - 1

You might wonder if we can use the existing quarter start dates directly. Yes. Since we already have a list of quarter start dates in column D, another option is to use the EOMONTH function directly with the spill range like this:

=EOMONTH(+D5#,2)

This formula simply shifts each quarter’s start date forward 2 months and returns the last day of that month. The result is exactly the same.

The plus sign (+) is needed before the spill range in this case. Without it, the formula will return a #VALUE! error. Some older functions, like EOMONTH, “resist” spilling when provided with a range. For example, EOMONTH(A1:A5,1) will return #VALUE even with valid dates in A1:A5. This limitation comes from certain functions expecting a single value instead of a range. The #VALUE! error is essentially reporting the range as an unexpected value. However, adding an operator in front of the reference will often fix the problem because it forces Excel to evaluate the expression first before the function runs. For an overview, see Dynamic Array Formulas in Excel .

Formula abbreviations

The quarter abbreviations seen in column F are generated with a formula like this in cell F5:

="Q"&MOD(SEQUENCE(12,,0),4)+1

At the core, this is a formula that repeats numeric sequences using the SEQUENCE function and the MOD function . We then concatenate a “Q” to the output. You can see the formula and result below:

Formula to generate quarter abbreviations - 2

Explanation

The DATEDIF function (Date + Dif) is a bit of an anomaly in Excel. A compatibility function that comes originally from Lotus 1-2-3, Excel will not help supply arguments when the function is entered. However, DATEDIF works in all modern versions of Excel and is a useful function for calculating the interval between two dates in years, months, and days.

In the example shown, the goal is to calculate age in years. The formula in E5 is:

=DATEDIF(D5,TODAY(),"y")

The first two arguments for DATEDIF are start_date and end_date. The start date comes from cell D5 (May 15, 2001) in the example. The end date is generated with the TODAY function. TODAY always returns the current date in Excel. As of this writing, the current date is November 24, 2020. The last argument in DATEDIF specifies the time unit. The DATEDIF function supports several options here , but for this example the goal is age in whole years, so we use “y” to specify complete years.

At this point, we can rewrite the formula as below:

=DATEDIF("15-May-2001","24-Nov-2020", "y")

Because Excel dates are actually serial numbers , the raw values are:

=DATEDIF(37026,44159,"y")

With these inputs, DATEDIF returns 19 as a final result.

Age on specific date

To calculate age on a specific date, replace the TODAY function with the target date. An easy and safe way to hardcode a specific date into a formula is to use the DATE function . For example, to calculate age as of January 1, 2021, you can use a formula like this:

=DATEDIF(D5,DATE(2022,1,1),"y") // returns 20

This formula will return Michael Chang’s age on January 1, 2022, which is 20.

Adult or Minor

To check a birthdate and return “Minor” or “Adult”, you can wrap the formula in the IF function like so:

=IF(DATEDIF(D5,TODAY(),"y")<18,"Minor","Adult")

The above formula is an example of nesting . Replace 18 with whatever age is appropriate.

Age in years, months, and days

To calculate age in years, months, and days, use three instances of DATEDIF like this:

=DATEDIF(A1,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"

The first instance of DATEDIF returns years, the second instance returns months, and the third instance returns days. This is an example of concatenation , and the result is a text string like this:

19y 6m 9d

Note start and end dates remain the same in all three DATEDIFs; only the unit is changed.

YEARFRAC with INT

Another option for calculating age from birthdate uses the YEARFRAC function together with the INT function in a formula like this:

=INT(YEARFRAC(D5,TODAY()))

YEARFRAC calculates a decimal number representing the fraction of a year between two dates. To work out the fraction of a year as a decimal value, Excel uses days between two dates. As above, the birthdate is provided as the start_date from cell D5, and today’s date is supplied as the end_date, courtesy of the TODAY function.

With a current date of November 24, 2020, the result from YEARFRAC for Michael Chang is:

19.5290896646133

Next, the INT function takes over and rounds down that number to the integer value, which is the number 19.

=INT(19.5290896646133) // returns 19

This formula appears perfectly logical and it works fine in most cases. However, YEARFRAC can return a number that isn’t correct on anniversary dates (birthdays). I’m not sure exactly why this happens, but it is related to how YEARFRAC uses days to determine fractional years, which is controlled by the basis argument . For example:

=YEARFRAC(DATE(1960,6,30),DATE(1962,6,30),1) // 1.998, should be 2
=YEARFRAC(DATE(1960,3,3),DATE(1964,3,3),1) // 3.998, should be 4

The bottom line is that the date DATEDIF formula is a safer and easier option when the goal is to report age in whole years.