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


i creating table in excel determine bi-annual dates input date.

example: if start date of agreement 9/1/2017 , ends 8/31/2018, bi-annual dates 2/28/18 , 8/31/2018. dates of service 2 months before end of agreement period, , 6 months before second service date (so 6/30/2018 , 12/31/2017 respectively).

formula this:

=if(isblank(o3), "",if(eomonth(a1, 0)=eomonth(o3, -2), "biannual", if(eomonth(a1, 0)=eomonth(o3, -8), "biannual", ""))) 

where a1 refers january, b1 february, , on thru december (l1). o3 agreement end date box, , static on sheet. formula work perfect me.

what trying formula cells @ top list months (jan-dec). need formula put date 1/31/2018 jan, 2/28/2018 feb, 9/30/2017 september (for current year since september has not passed). actual day needs last day of month, , if month has passed, year should next year. have been playing date function, cannot nailed down.

what have far - january 2018:

=date(year(today()+365), month(42766), day(eomonth(42766, 0))) 

this works, not each month in 2018. need year change after month has passed.

i feel i'm either on complicating things, or need way more complex formula. please help.

in a1 place following formula , copy right l1 or far need go

=eomonth($o$2,column(a1)-1) 

it display end of month dates starting starting month of contract , increasing 1 month each column move right.

in image below, same formula in row 1 , row 2. row 1 choose custom format instead of date , set custom format mmmm. 4 m's give full month, , 3 m's give 3 starting letters of month.

enter image description here


Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -