You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges, and won't let you use something like MONTH to extract just the month number from dates. So, we...
The choose function uses the first argument to "select" remaining elements. For example, in a scheme where 1 = small, 2 = medium, and 3 = large, this formula will "map" the number 2 to "medium".
=CHOOSE(2,"small...
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 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"....
By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal year begins in July, then the date August 1, 2018 is in fiscal year 2019.
The year function first returns the year from the date 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...
In this case, Excel extracts the month from the date in cell B6 as a number, and the month in the cell C6 as a number, then tests for equivalency using the equal sign. Both dates are in January, so the formula is...
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...
In this example, we need to construct logic that filters data to include:
account begins with "x" AND region is "east", and month is NOT April.
The filtering logic of this formula (the include argument) is created by...
This data set contains over 2900 records, and the formula above uses two named ranges:
dates = B5:B2932
amounts = E5:E2932
Inside the SUMPRODUCT function, the MONTH function is used to extract the month number for...
The MONTH function takes just one argument, the date from which to extract the month. In the example shown, the formula is:
=MONTH(B4)
where B4 contains the dateJanuary 5, 2016. The MONTH function returns the number...
Do you want to convert the date into a month name?
If you want to convert the date value to a text value, you can use the TEXT function with a custom number format like "mmmm", or "mmm". In the example shown, the...
This formula relies on the FILTER function to retrieve data based on a logical test created with the MONTH function. The array argument is provided as the named range "data", which contains the full set of data without...
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...
Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use boolean logic to...