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.

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

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.

-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))
  • 2
    This formula will lose a day every four years or so – Alex M Feb 15 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 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 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.