+ Reply to Thread
Results 1 to 3 of 3

Semi Annual Dates to cross over to next year

  1. #1
    Registered User
    Join Date
    07-19-2016
    Location
    Colorado
    MS-Off Ver
    Microsoft Office 15
    Posts
    22

    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. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    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. #3
    Registered User
    Join Date
    07-19-2016
    Location
    Colorado
    MS-Off Ver
    Microsoft Office 15
    Posts
    22

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Semi Annual Dates
    By PsychicFish in forum Excel General
    Replies: 1
    Last Post: 07-19-2016, 11:21 PM
  2. [SOLVED] "Semi-dynamically" displaying system date, year-by-year
    By wspencer11 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-31-2016, 02:56 PM
  3. Replies: 1
    Last Post: 05-07-2014, 06:19 PM
  4. [SOLVED] Show Annual Year
    By ps65 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-14-2013, 11:05 AM
  5. Semi-annual issues
    By Sami Kaasalainen in forum Excel General
    Replies: 7
    Last Post: 11-26-2010, 12:02 PM
  6. Rounding to Semi-Annual Dates
    By Twisty20 in forum Excel General
    Replies: 2
    Last Post: 07-25-2007, 05:37 PM
  7. Semi Annual Counter
    By ExcelMonkey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-21-2006, 06:10 PM

Bookmarks

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