Help with formula on due dates

Copper Contributor
I have a start date (Will always be the end of a month (ex 12/31/2019 or 09/30/2018) and I need the formula to calculate the date 4 months later on the 15th of the month. If the 15th is a weekend then the 16th or 17th. It always needs to be the 15th unless on a weekend.
19 Replies

@Akmcg2 

Let's say the start date is in A2. The following formula hopefully returns the date that you want:

 

=WORKDAY(EOMONTH(A2,3)+14,1)

I was so close! I wasn’t using EOMONTH can you explain?

I have another thing too I have an agreement date and then I want the next due date After that date. The due date would be the month and day of the formula you just gave me but the year would be different. Any ideas?

@Akmcg2 

EOMONTH(date, n) returns the last day of the month n months after date. So EOMONTH(A2,3) is the last day of the month 3 months after the date in A2.

If you want the date one year (12 months) after the date returned by the formula, you can use

 

=WORKDAY(EOMONTH(A2,15)+14,1)

So it needs to match the month that it would normally be due

So if it’s normally due 1/15(YYYY) Figured from other formula

But there was an agreement date on 2/13/2020 - the formula should return 1/15/2021

Also what does the 14,1 do? I want to understand so hopefully I can get better at these lol

@Akmcg2 

Let's say A2 contains 29-Jul-2020.

 

EOMONTH(A2,3) is the last day of the month 3 months after that, i.e. 31-Oct-2020.

 

EOMONTH(A2,3)+14 adds 14 days to that, i.e. 14-Nov-2020.

 

WORKDAY(EOMONTH(A2,3)+14,1) returns the first (1) working day after that. Since 15-Nov-2020 is a Sunday, the formula returns 16-Nov-2020, a Monday.

@Akmcg2 


@Akmcg2 wrote:
So if it’s normally due 1/15(YYYY) Figured from other formula
But there was an agreement date on 2/13/2020 - the formula should return 1/15/2021


I'm afraid I don't understand. Could you provide a more detailed example? Thanks in advance.

So for example, a person files forms on a fiscal year of 09 (09/30/2019) Per the formula you gave me their items are due 01/15/2020.

Now they filed paperwork on 2/13/20 - I need to know what is the next due date after the date they filed the paperwork. So it would be 01/15/2021. Because the due dates are always 1/15 - just different years because of the fiscal year of 09. Does that make sense?
@Hans Vogelaar Any ideas??

@Akmcg2 

That would be

=WORKDAY(EOMONTH(A2,15)+14,1)

@Akmcg2  IF you are looking for Jan 15th of the next year you can use:

 

=DATE(YEAR(A1)+1,1,15)

 

or to avoid weekends etc...

=WORKDAY( DATE(YEAR(A1)+1,1,14),1)

So it’s not alwaya one. I need the year from the date the agreement was dated and then the month it’s due is based off of a different cell

So cell k1 is the agreement date
Cell L3 is the due date

Example: agreement date 2/13/2020
Due date: 10/16/2017

I need a formula that will show the next due date after the agreement date. So with this example the answer should be 10/15/2020.
This does not return the correct answer

So it’s nots one. I need the year from the date the agreement was dated and then the month it’s due is based off of a different cell

So cell k1 is the agreement date
Cell L3 is the due date

Example: agreement date 2/13/2020
Due date: 10/16/2017

I need a formula that will show the next due date after the agreement date. So with this example the answer should be 10/15/2020.

@Akmcg2  I finally get it.  There might be a more elegant formula, but this should work:

=IF(DATE(YEAR(K1),MONTH(L3),DAY(L3))>K1,DATE(YEAR(K1),MONTH(L3),DAY(L3)),DATE(YEAR(K1)+1,MONTH(L3),DAY(L3))

 

It needs to be the 15th and a work day also

@Akmcg2 ugh, forgot about that part:

 

=WORKDAY(DATE(YEAR(K1)+(DATE(YEAR(K1),MONTH(L3),15)<K1),MONTH(L3),14),1)

 

I even made it a little more elegant, lol. 

@Akmcg2 

How about

 

=WORKDAY(DATE(YEAR(L3)+L5+1,MONTH(L3),14),1)

Thank you!
Thank you!!