# Semi Annual Dates to cross over to next year

1. ## Semi Annual Dates to cross over to next year

I am trying to have excel return a Semi Annual date (my two dates are 1 April and 1 November) from a date in cell A1 but my current formula will not switch to the next year once I get to December. The year of my date only changes to my next Semi Annual date and year once I am in January of the next year.

Example: Cell A1 Date: October 1, 2016 returns the correct Semi Annual date of November 1, 2016 based on the formula below:

=IF(MONTH(A1)>4,DATE(YEAR(A1),11,1),DATE(YEAR(A1),4,1))

Once the date is December 1, 2016 in cell A1 I would like it to return 1 April, 2017.

Please let me know if you can help the formula works great except in December.

2. ## Re: Semi Annual Dates to cross over to next year

If it's semi-anually, shouldn't it be April and October, instead of November?

Just add a condition that checks for month = 12 and calculate the date accordingly.

=IF(MONTH(A2)=12,DATE(YEAR(A2)+1,4,1),IF(MONTH(A2)>4,DATE(YEAR(A2),11,1),DATE(YEAR(A2),4,1)))

3. ## Re: Semi Annual Dates to cross over to next year

Thank you so much!! it should be October but I am using this to populate due dates for reports and we don't get that particular report until November. Thank you again I would have never thought of that!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1