Problem with displaying a date range for leap years

[Parmo]

Hi,

I've just finised an interest calculator for my work and have just noticed a small problem before sending it for proofing. The sheet allows the user to select a number of days to calulate interest at a given balance and rate. The actual range is then shown to the right. For example, 5 days of Jan from 1st actually cover 1 Jan - 6 Jan on our system. I've used the following formula for this:

=IF((C12)>0, DATE(YEAR(0),MONTH(B10),DAY(C12))," ")

for 1st choice

=IF((D12)>0,DATE(YEAR(0),MONTH(B10),DAY(D12)+1)," ")

for 2nd choice. The IF arguement is simply a presentation choice, I don't want anything displayed in the cells unless some data is present.

Anyway, this all works fine with the exception of February. The sheet allows calculations with a leap year with 29 days. This too works great. However, If I wanted to have a normal calculation of 28 days of Feb and then continue in March, the date range show 1 Feb - 29 Feb instead of 1 Mar - due to the
DAY(D12)+1 code. So, what I need is for a formula to recognise that the user has selected all the days of Feb and then put in the date range area that it goes to 1 Mar instead of 28+1.

Something that might be useful: in the calculations involving leap year, I have a checkbox which changes B17 to 29 if checked or leaves it as 28 if not, so the calc for Febs interest is Balance x rate x (no of days / B17). If i could get a formula that says something like:
IF B17 = D16 (where the end date is) then instead of displaying date +1, display "1 Mar". (would this involve an IF (AND... solutions maybe??)

Apologies for the convoluted explanation. I've attached a screenshot of the sheet, I can attach the sheet itself if needs be.

MANY thanks for any replies.
Regards
Mike

[oldchippy]

Here's a formula to check for leap years, adapt to suit your range

=IF(MONTH(DATE(YEAR(A20),2,29))=2,TRUE,FALSE)

[daddylonglegs]

If B17 = 29 when you want to treat year as a leap year then try

=IF(D12="","",DATE((B17=28)+0,MONTH(B10),D12))

This effectively treats the year as 1900 (which is a leap year for excel purposes) when you have 29 in B17 and 1901 (which isn't a leap year) when you have 28

Note: you don't need DAY(D12), just D12 is sufficient