Forum Discussion
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...
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?
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?
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)
- Also what does the 14,1 do? I want to understand so hopefully I can get better at these lol
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.
- 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- 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?