1

I'm working on a formula to align with our business' invoicing policy. The rules are that, if an order is placed, the first invoice is generated same day.

=IF(OR(I90='2b - LookUp Values-UI'!K185,I90=TODAY()),"PASS","FAIL")

The above is sufficient for that requirement; however, I need to create another formula that will take the original invoice date into account and schedule the 2nd and 3rd invoices for exactly 1 and 2 years later. (e.g. If the initial invoice generates 2/15/2019, they Y2 Invoice will automatically generate 2/15/2020, and the Y3 will generate 2/15/2021)

Any help is greatly appreciated.

Improve this question

3 Answers 3

Reset to default
4

I understand that I90 holds your invoice date?

This formula will add a year to the date in I90.

=DATE(YEAR(I90)+1,MONTH(I90),DAY(I90))

This formula will add two years to the date in I90.

=DATE(YEAR(I90)+2,MONTH(I90),DAY(I90))

Improve this answer
3

Here’s an answer that is almost functionally equivalent to Alex M’s, but less typing:

=EDATE(I90, 12)

The 12 is (obviously) a number of months.  This handles leap year, but differently from Alex’s answer:

+--------------+---------------+---------------+
|  Input Data  | Alex's answer |   My answer   |
|   (“I90”)    |  (YEAR()+1…)  |    (EDATE)    |
+--------------+---------------+---------------+
|  2/27/2020   |   2/27/2021   |   2/27/2021   |
|  2/28/2020   |   2/28/2021   |   2/28/2021   |
|  2/29/2020   |   2/28/2021   |    3/1/2021   |
|   3/1/2020   |    3/1/2021   |    3/1/2021   |
|   3/2/2020   |    3/2/2021   |    3/2/2021   |
+--------------+---------------+---------------+

Apparently EDATE has been in Excel since 2007.

Improve this answer
-1

You can add to dates and today like so:

=Today()+365

=DATE(2019,2,14)+365

To account for leap years you can add only to the year with:

=DATE(YEAR(A1)+2, MONTH(A1), DAY(A1))
Improve this answer
3
  • 2
    This formula will lose a day every four years or so
    – Alex M
    Feb 15, 2019 at 0:56
  • 1
    @AlexM I saw your comment about not accounting for leap years but did not see that you had submitted an answer and it seems our independent research came up with the same/similar answers. Thank you for the note of where my formula would not work, and I apologize if I stepped on your toes sir.
    – Brian
    Feb 15, 2019 at 19:11
  • 1
    Thanks for the reply, I retract the allegation (and have deleted my comment). I'll take the nearly identical answer as corroboration that I worked out the correct method, then. Cheers
    – Alex M
    Feb 15, 2019 at 19:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .