Forum Discussion

macjr911's avatar
macjr911
Copper Contributor
Apr 12, 2020

Auto monthly due date update

If my bill is due April 15 2020, so when date is April 16, 2020,  I want the cell to auto change to May 15 2020. and evry month after that

excel

3 Replies

  • CA-Santosh's avatar
    CA-Santosh
    Copper Contributor
    Apr 12, 2020

    Hi,

     

    Try this formula - =DATE(YEAR(A2),IF(DAY(A2)<=15,MONTH(A2),MONTH(A2)+1),15)

     

    Assumption made: 

    1. Bill date (from the 1st day of month to 15th day of month) - Due date falls on 15th day of the same month

    2. Bill date (from the 16th day of month to month end) - Due date falls on 15th day of the next month

    3. Assume A2 cell contains the Bill date.

     

    • Arsi11's avatar
      Arsi11
      Copper Contributor
      Aug 30, 2021
      Hi so i need to set an automatic yearly renewal date. the lease ends 01/01/2022 and I have set if renew column state 'yes' the renewal date is automatically increased by a year. if 'no'. it does not renew

Resources