Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula in case you want or need more control and flexibility.
To allow a...

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 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))...

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 count dates in a given year, you can use the SUMPRODUCT and YEAR functions. In the example shown, the formula in E5 is:
=SUMPRODUCT(--(YEAR(dates)=D5))
where "dates" the a named range B5:B15.
How this formula...

Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula if you need more control or flexibility.
To allow a user to enter...

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 year from a date, you can use the YEAR function. In the generic form of the formula above, the date must be in a form that Excel recognizes as a valid date.
How the formula works
The YEAR...

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 calculate the percent remaining in a year, based on a given date, you do so with a formula based on the YEARFRAC function.
In the example, the formula in C5, which calculates percentage of year remaining...

To get the nth day of year based on a given date, you can use a formula based on the DATE and YEAR functions. In the example shown, the formula in C5 is:
=B4-DATE(YEAR(B4),1,0)
With the date "June 1, 2016" in cell B4...

If you need to convert a date to a Julian date format in Excel, you can do so by building a formula that uses the TEXT, YEAR, and DATE functions.
Background
"Julian date format" refers to a format where the year value...

To get a fiscal year from a date, you can use a formula based on the YEAR and MONTH functions. In the example shown, the formula in D5 is:
=YEAR(B5)+(MONTH(B5)>=C5)
How this formula works
By convention a fiscal...

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...