# Help with formula on due dates

Occasional Contributor

# Help with formula on due dates

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

# Re: Help with formula on due dates

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)

# Re: Help with formula on due dates

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?

# Re: Help with formula on due dates

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)

# Re: Help with formula on due dates

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

# Re: Help with formula on due dates

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

# Re: Help with formula on due dates

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.

# Re: Help with formula on due dates

@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.

# Re: Help with formula on due dates

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?

# Re: Help with formula on due dates

@Hans Vogelaar Any ideas??

# Re: Help with formula on due dates

That would be

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

# Re: Help with formula on due dates

@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)

# Re: Help with formula on due dates

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.

# Re: Help with formula on due dates

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.

# Re: Help with formula on due dates

@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))``

# Re: Help with formula on due dates

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

# Re: Help with formula on due dates

@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.