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