=IF(ISBLANK([@[Declaration Date]]),"", DATE(YEAR([@[Declaration Date]])+1,MONTH(3),DAY(31)))



I wrote the above to calculate the due date for new applications based on the declaration date on an event. The due date is always 3/31 in the calendar year following the year after the event, no matter what month/day.

The above formula is returning the correct year and day, but no matter what number I put after MONTH, the result is 1/31/XXXX rather than 3/31/XXXX. Excel not returning any error message on formula.

What did I do wrong?

Thanks.

=IF(ISBLANK([@[Declaration Date]]),"", DATE(YEAR([@[Declaration Date]])+1,3,31))
?

Re: "no matter what number I put after MONTH"
For silliness, try 65.

Okay, you HAVE to explain to me why that worked!!!!

It's better to explain why your formula didn't work!
MONTH(3) returns the month of date 1/3/1900 (i.e. 1, as it will be January);
DAY(31) returns the day of date 1/31/1900.

The syntax of DATE() function is:
DATE(YearNumber, MonthNumber, DayNumber)