Splitting dates not working

[rriiicchh]

I have been bashing my head against this problem for a few hours and just when I thought I'd cracked it running some validation against the formula showed it adds up 1 year as 364 days so I have just shifted my problem.

In short I have two dates 02/11/2013 and 04/06/2014 and I need a formula to split the number of days between (and including) those dates between year end which in this case is always the 1st of April.

For example with the above it should be 149 and 65 and as another example if the dates were 31/03/2013 and 01/04/2014 then the result would need to be 1 and 1

With a field in the spreadsheet **FinancialYear** holding the year which I need to apportion the days between.

The spreadsheet looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]StartDate[/TD]
[TD]EndDate[/TD]
[/TR]
[TR]
[TD]02/11/2013[/TD]
[TD]04/06/2014[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Financial Year[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]2012-2013[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]2013-2014[/TD]
[TD]Formula[/TD]
[/TR]
</tbody>[/TABLE]





The formula I currently have is

=IF(IF(MONTH(NOW())<4,YEAR(NOW())-1&"-"&(YEAR(NOW())),YEAR(NOW())&"-"&(YEAR(NOW())+1))=**FinancialYear**,MAX(0,(MIN(**EndDate**,DATEVALUE("31/03/"&RIGHT(**FinancialYear**,4)))-MAX(**StartDate**,DATEVALUE("01/04/"&LEFT(**FinancialYear**,4)))+1)),MAX(0,(MIN(**EndDate**,DATEVALUE("31/03/"&RIGHT(**FinancialYear**,4)))-MAX(**StartDate**,DATEVALUE("01/04/"&LEFT(**FinancialYear**,4))))))

I know the above formula is wrong so I am hoping that someone can show me the error of my ways as this is the first time in a long time that I haven't been able to resolve an excel issue. I hate working with dates. Any help will be greatly appreciated.

 

[mole999]

365 should occur if you run 00:00:00 1/4/ to 23:59:59 31/3, whats happening is (I believe) the date is a whole number only so to get the three six five, just getting into 31/3 doesn't give you the day, you need to add 1 to get to your comfort zone

 

[Andrew Poulsom]

Is it?

Excel 2010
	A	B
1	StartDate	EndDate
2	31/03/2013	01/04/2013
3
4	Financial Year	Days
5	2012-2013	1
6	2013-2014	1
Sheet1

Cell Formulas
Range	Formula
B5	=MIN(B$2,DATE(RIGHT(A5,4),3,31))-MAX(A$2,DATE(LEFT(A5,4),4,1))+1