Excel Date table to determine next Bi-annual date from Input date

0
votes

I am creating a Table in excel to help determine what the Bi-annual dates would be from an input date.

Example: If the start date of an agreement is 9/1/2017 and Ends 8/31/2018, the Bi-annual dates would be 2/28/18 and 8/31/2018. Dates of service would be 2 months before the end of the agreement period, and six months before the second service date (so 6/30/2018 and 12/31/2017 respectively).

Formula for this:

=IF(ISBLANK(O3), "",IF(EOMONTH(A1, 0)=EOMONTH(O3, -2), "BIANNUAL", IF(EOMONTH(A1, 0)=EOMONTH(O3, -8), "BIANNUAL", "")))

Where A1 refers to January, B1 would be February, and so on thru to December (L1). O3 is the Agreement End Date box, and will be static on the sheet. This formula work perfect for me.

What I am trying to get is a formula for the cells at the top that list the months (Jan-Dec). I need a formula that will put the date as 1/31/2018 for Jan, 2/28/2018 for Feb, 9/30/2017 for September (for the current year since September has not passed). The actual day needs to be the last day of the month, and if that month has passed, then the year should be for next year. I have been playing with the DATE function, but cannot get it nailed down.

What I have so far - January 2018:

=DATE(YEAR(TODAY()+365), MONTH(42766), DAY(EOMONTH(42766, 0)))

This works, but not each month will be in 2018. I need the year to change only after the month has passed.

I feel like I'm either over complicating things, or I need a way more complex formula. Please help.

2
excelexcel-formulaoffice365
can you provide some example data? When you say things like January in A1 does that mean the text January, or and excel date formatted to display the month? Are you looking for a formula that will Generate A1 to L1 Automatically based on start and end dates? Just got a little lost in the description you gave. – Forward Ed

2 Answers

0
votes

In A1 place the following formula and copy right to L1 or as far as you need to go

=EOMONTH($O$2,COLUMN(A1)-1)

It will display the end of month dates starting with the starting month of the contract and increasing by 1 month for each column you move right.

In the image below, it is the same formula in row 1 and row 2. Row one I choose custom format instead of date and set the custom format to mmmm. 4 m's will give you the full month, and 3 m's will give you the 3 starting letters of the month.

enter image description here

0
votes

I actually figured this out this morning just playing with the IF function. My goal was to have the sheet update itself without having to change the dates every time your open it. So that the file could be shared with others and all you would have to enter is the end of the contract date, and it will list out Biannual, Tri-annual, and Quarterly months (see image).

Formula:

=IF(DATE(YEAR(TODAY()), MONTH(42766), DAY(EOMONTH(42766, 0)))<TODAY(), DATE(YEAR(TODAY()+365), MONTH(42766), DAY(EOMONTH(42766, 0))), DATE(YEAR(TODAY()), MONTH(42766), DAY(EOMONTH(42766, 0))))

Where I used the serial for each month (in this case 1/31/2017, as I didn't need to worry about the year)

Results