Auto monthly due date update

%3CLINGO-SUB%20id%3D%22lingo-sub-1301212%22%20slang%3D%22en-US%22%3EAuto%20monthly%20due%20date%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1301212%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20my%20bill%20is%20due%20April%2015%202020%2C%20so%20when%20date%20is%20April%2016%2C%202020%2C%26nbsp%3B%20I%20want%20the%20cell%20to%20auto%20change%20to%20May%2015%202020.%20and%20evry%20month%20after%20that%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1301212%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1301249%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20monthly%20due%20date%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1301249%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%20-%26nbsp%3B%3DDATE(YEAR(A2)%2CIF(DAY(A2)%26lt%3B%3D15%2CMONTH(A2)%2CMONTH(A2)%2B1)%2C15)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssumption%20made%3A%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Bill%20date%20(from%20the%201st%20day%20of%20month%20to%2015th%20day%20of%20month)%20-%20Due%20date%20falls%20on%2015th%20day%20of%20the%20same%20month%3C%2FP%3E%3CP%3E2.%20Bill%20date%20(from%20the%2016th%20day%20of%20month%20to%20month%20end)%20-%20Due%20date%20falls%20on%2015th%20day%20of%20the%20next%20month%3C%2FP%3E%3CP%3E3.%20Assume%20A2%20cell%20contains%20the%20Bill%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1301290%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20monthly%20due%20date%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1301290%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2698176%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20monthly%20due%20date%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2698176%22%20slang%3D%22en-US%22%3EHi%20so%20i%20need%20to%20set%20an%20automatic%20yearly%20renewal%20date.%20the%20lease%20ends%2001%2F01%2F2022%20and%20I%20have%20set%20if%20renew%20column%20state%20'yes'%20the%20renewal%20date%20is%20automatically%20increased%20by%20a%20year.%20if%20'no'.%20it%20does%20not%20renew%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies

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.

 

Thank you.

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