To filter and extract data based on multiple complex criteria, you can use the FILTER function with a chain of expressions that use boolean logic. In the example shown, the formula in G5 is:
=FILTER(B5:E16,(LEFT(B5:...
If you want to calculate the quarter (i.e. 1,2,3,4) from a date, you can use the ROUNDUP function together with MONTH.
In the example shown, the formula in cell C5 is:
=ROUNDUP(MONTH(B5)/3,0)
How this formula works...
To add a given number of years to a date, you can use a formula based on the DATE function, with help from the YEAR, MONTH, and DAY functions.
In the example shown, the formula in D5 is:
=DATE(YEAR(B5)+C5,MONTH(B5),...
To generate a series of dates by year, you can use the SEQUENCE function together with YEAR, MONTH, and DAY functions. In the example shown, the formula in E5 is:
=DATE(SEQUENCE(12,1,YEAR(B5)),MONTH(B5),DAY(B5))...
If you need to test two dates to see they both have the same month and year, you can do so with a simple formula that uses the MONTH and YEAR functions.
How the formula works
In the example, the formula in cell D6...
To sum data by month, ignoring year, you can use a formula based on the SUMPRODUCT and MONTH functions. In the example shown, the formula in H6 is:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
The result is a total of all...
If you want to test whether the year of a certain date is a leap year, you can use a formula that uses the MONTH, YEAR, and DATE functions.
In the example shown, the formula in cell C5 is:
=MONTH(DATE(YEAR(B5),2,29...
If you need to extract the month from a date, you can use the MONTH function. In the generic form of the formula above, the date must be in a form that Excel recognizes as a valid date.
How this formula works
The...
If you want to calculate the fiscal quarter from a date, and the fiscal quarter starts in a month other than January, you can use a formula based on the CHOOSE function.
Note: if you just need to calculate a "normal"...
To generate a dynamic series of dates with a formula that increases by one month from a single start date, you can use a formula based on DAY, MONTH, YEAR, and DATE functions.
How the formula works
In the example, B6...
If you need to get the month name (i.e. January, February, March, etc.) from a date, you have several options depending on your needs.
Do you just want to display the month name?
If you only want to display a month...
To count the number of birthdays in a list, you can use a formula based on the SUMPRODUCT and MONTH functions. In the example shown, E5 contains this formula:
=SUMPRODUCT(--(MONTH(birthday)=D5))
This formula counts...
If need to generate a dynamic series of dates with a formula that increase by one year from a single start date, you can do so with a formula that uses the DAY, MONTH, YEAR, and DATE functions.
How the formula works...
To lookup data based on multiple complex criteria, you can use the XLOOKUP function with multiple expressions based on boolean logic. In the example shown, the formula in G5 is:
=XLOOKUP(1,(LEFT(B5:B16)="x...
To test two dates to see they both have the same month, you can do so with a simple formula that uses the MONTH function. In the example shown, the formula in cell D6 is:
=MONTH(B6)=MONTH(C6)
How the formula works...