1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • Conditional formatting on anniversary

    From Garrett Oler@21:1/5 to All on Thu May 19 18:22:09 2022
    Hi

    I am at a loss for how to make this work.

    I have a cell with the date of the start of a service subscription.

    I want to highlight a cell every 11th month so they are reminded that the subscription will renew on the next month. I have figured out how to make it work once, but not repeatable year over year.

    Any ideas?
    Thanks,
    Garrett

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Fri May 20 11:22:03 2022
    Hi Garrett,

    Am Thu, 19 May 2022 18:22:09 -0700 (PDT) schrieb Garrett Oler:

    I have a cell with the date of the start of a service subscription.

    I want to highlight a cell every 11th month so they are reminded that the subscription will renew on the next month. I have figured out how to make it work once, but not repeatable year over year.

    try:
    =MONTH(A1)=MONTH(TODAY())-1


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Michael Soyka@21:1/5 to Claus Busch on Fri May 20 13:09:30 2022
    On 05/20/2022 5:22 AM, Claus Busch wrote:
    Hi Garrett,

    Am Thu, 19 May 2022 18:22:09 -0700 (PDT) schrieb Garrett Oler:

    I have a cell with the date of the start of a service subscription.

    I want to highlight a cell every 11th month so they are reminded that the subscription will renew on the next month. I have figured out how to make it work once, but not repeatable year over year.

    try:
    =MONTH(A1)=MONTH(TODAY())-1


    Regards
    Claus B.
    Assuming the start date is in A1, shouldn't it be:
    =MONTH(A1)-1=MONTH(TODAY())
    instead?

    For example, if the start date is in May, the reminder should always go
    out in April, assuming I understand the OP's desire.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Michael Soyka@21:1/5 to Michael Soyka on Fri May 20 13:27:12 2022
    On 05/20/2022 1:09 PM, Michael Soyka wrote:
    On 05/20/2022 5:22 AM, Claus Busch wrote:
    Hi Garrett,

    Am Thu, 19 May 2022 18:22:09 -0700 (PDT) schrieb Garrett Oler:

    I have a cell with the date of the start of a service subscription.

    I want to highlight a cell every 11th month so they are reminded that
    the subscription will renew on the next month. I have figured out how
    to make it work once, but not repeatable year over year.

    try:
    =MONTH(A1)=MONTH(TODAY())-1


    Regards
    Claus B.
    Assuming the start date is in A1, shouldn't it be:
       =MONTH(A1)-1=MONTH(TODAY())
    instead?

    For example, if the start date is in May, the reminder should always go
    out in April, assuming I understand the OP's desire.

    Actually, that doesn't work if the start date is in January. Modulo
    arithmetic is required:

    =MOD(MONTH(A1)+11-MONTH(TODAY()),12)=0

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)