The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here. Zodiac signs are used in...

The core of this formula is the DATE function, which will automatically adjust to month and year values that are out of range. In the formula, year is passed into date unchanged, along with 2 for month (February) and 29...

This formula takes advantage of the fact that dates are just sequential numbers in Excel. It determines the last day of the previous year and subtracts that value from the original date with this formula:
=B5-DATE(...

Data validation rules are triggered when a user adds or changes a cell value.
This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function.
When a user...

Working from the inside out, the YEAR, MONTH, and DAY functions extract those respective date components:
=YEAR(B5) // 1960
=MONTH(B5) // 3
=DAY(B5) // 8
At the outer level, the DATE function simply reassembles the...

The goal in this example is to return the amount of time completed in a year as a percentage value, based on any given date. In other words, when given the date July 1, 2021, the formula should return 50% since we are...

The YEAR function takes just one argument, the date from which you want to extract the year. In the example, the formula is:
=YEAR(B4)
B4 contains a date value for January 5, 2016. The YEAR function returns the...

Data validation rules are triggered when a user adds or changes a cell value.
This custom validation formula uses the WEEKDAY function to get a numeric value, 1-7, corresponding to to a week beginning Monday (1) and...

In the example, B6 is the hard-coded start date and the formula in B7 is:
=DATE(YEAR(B6)+1,MONTH(B6),DAY(B6))
To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then...

In the example, B6 is the hard-coded start date and the formula in B7 is:
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then...

The YEAR function extracts the year from a valid date. In this case, we are giving YEAR and array of dates in the named range "dates", so we get back an array of results:
{2018;2017;2019;2019;2017;2019;2017;2019;2019...

In the example, the formula in cell D6 contains this formula:
=MONTH(B6)&YEAR(B6)=MONTH(C6)&YEAR(C6)
In this case, Excel extracts the month from the date in cell B6 as numbers, then concatenates them...

The SEQUENCE function is a dynamic array function that can generate multiple results. Like other dynamic array functions, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range"....

In this example, the goal is to return the fiscal year for any given date, where a fiscal year starts in July as seen in the worksheet. By convention a fiscal year is denoted by the year in which it ends. So, if a...

The goal of this example is to sum amounts by fiscal year, when the fiscal year begins in July. The first approach is an self-contained formula based on the SUMPRODUCT function. The second method uses SUMIF with column...