I have a set of start and end dates that can cover several years. These are award dates for grants.

For any pair of start and end dates I want to be able to calculate how many days there are for a particular fiscal year.

In my organization the fiscal year is from 6/1/xx to 5/31/xx

So for example if I have a set of dates from 2/7/2011 - 2/6/2016 how many days are there in the fiscal year ending 5/31/11.

I know by looking to count the days from 2/7/11 to 5/31/11, its 113. But a formula escapes me. I've tried different kinds of IF statements but I can't crack it. Any solutions?

Let's say the start date (2/7/2011) is in B1 and the end date (2/6/2016) in B2, while the year (2010) is in F1.

The formula =MAX(MIN(B2,DATE(F1+1,5,31))-MAX(B1,DATE(F1,6,1)),0) will return the number of days that you want.

So if you have the start date in B1, you could use:

=DATE(YEAR(B1), 5, 31)-B1