Automatic date calculations

[Luigi802]

Below is a list of bills, I'm trying to figure out a way to have the Due Date column automatically calculate each month after the date is passed i.e. today is October 30th, so every due date before that will automatically show the dues date in NOV. Like bill # 1 is due the first of this month, obviously it's passed that so it should automatically switch to Nov 1st. I've tried many different formulas with no luck, any suggestions?

[TABLE="width: 350"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Monthly Payment[/TD]
[TD]Day of month due[/TD]
[TD]Due date[/TD]
[/TR]
[TR]
[TD]Bill 1[/TD]
[TD]$604.07[/TD]
[TD]1[/TD]
[TD]1-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 2[/TD]
[TD]$200.56[/TD]
[TD]1[/TD]
[TD]1-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 3[/TD]
[TD]$40.00[/TD]
[TD]1[/TD]
[TD]1-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 4[/TD]
[TD]$25.50[/TD]
[TD]3[/TD]
[TD]3-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 5[/TD]
[TD]$310.87[/TD]
[TD]4[/TD]
[TD]4-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 6[/TD]
[TD]$20.00[/TD]
[TD]5[/TD]
[TD]5-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 7[/TD]
[TD]$96.95[/TD]
[TD]6[/TD]
[TD]6-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 8[/TD]
[TD]$55.00[/TD]
[TD]11[/TD]
[TD]11-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 9[/TD]
[TD]$40.00[/TD]
[TD]12[/TD]
[TD]12-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 10[/TD]
[TD]$112.23[/TD]
[TD]17[/TD]
[TD]17-Nov-14[/TD]
[/TR]
[TR]
[TD]Bill 11[/TD]
[TD]$110.00[/TD]
[TD]30[/TD]
[TD]30-Oct-14[/TD]
[/TR]
[TR]
[TD]Bill 12[/TD]
[TD]$600.00[/TD]
[TD]30[/TD]
[TD]30-Oct-14[/TD]
[/TR]
[TR]
[TD]Bill 13[/TD]
[TD]$160.50[/TD]
[TD]31[/TD]
[TD]31-Oct-14[/TD]
[/TR]
[TR]
[TD]Bill 14[/TD]
[TD]$7.99[/TD]
[TD]31[/TD]
[TD]31-Oct-14[/TD]
[/TR]
</tbody>[/TABLE]

 

[konew1]

If you have a column of dates and you want them to change when they are prior to todays date then you woll need come code.
If yiu want to have the original dates in one column and another column to show the revised date because it is past due then with the original date on A1 and the new date in B1
B1= Max(a1,TODAY())

 

[madhumita]

Hi,

You can use this formula : "=DATE(YEAR(TODAY()),MONTH(TODAY())+1,B2)" where "B2" contains your "Day of month due"

 

[Luigi802]

Not quite what I'm looking for, this is the formula I've been using =DATEVALUE(CONCATENATE(MONTH(TODAY()),"-",[@[Day of month due]],"-",YEAR(TODAY()))) Where "[@[Day of month due]]" is the number in column under the same name which is just a number value indicating the number of the day it's due. The problem is that once said date is passed I want it to show the date for the next month. So basically what I have written in the due date column right now would automatically recalc every time the dues date is passed. Does that make sense?

 

[madhumita]

HI,

Yea got it!

You can use this formula :<today(), date(year(today()),month(today())+1,b2),="" date(year(today()),month(today())+2,b2))
<today(),
<today(), date(year(today()),month(today())+1,d29),="" date(year(today()),month(today())+2,d29))
<today(), date(year(today()),month(today())+1,d29),="" date(year(today()),month(today())+2,d29))[="" quote]=""
<today(), date(year(today()),month(today())+1,d29),="" date(year(today()),month(today())+2,d29))[="" code]
<today(), date(year(today()),month(today())+1,b2),="" date(year(today()),month(today())+2,b2))[="" code]

IF(DATE(YEAR(TODAY()),MONTH(TODAY()),B2)<TODAY(), DATE(YEAR(TODAY()),MONTH(TODAY())+1,B2), DATE(YEAR(TODAY()),MONTH(TODAY())+2,B2))
<today(), date(year(today()),month(today())+1,d29),="" date(year(today()),month(today())+2,d29))"
Let me know if this works.


</today(),></today(),></today(),></today(),></today(),></today(),></today(),>

 

[madhumita]

HI,


Yea got it!


You can use this formula


IF(DATE(YEAR(TODAY()),MONTH(TODAY()),B2)<TODAY(), DATE(YEAR(TODAY()),MONTH(TODAY())+1,B2), DATE(YEAR(TODAY()),MONTH(TODAY())+2,B2))



Let me know if this works.

 

[Luigi802]

HI,


Yea got it!


You can use this formula


IF(DATE(YEAR(TODAY()),MONTH(TODAY()),B2)<TODAY(), DATE(YEAR(TODAY()),MONTH(TODAY())+1,B2), DATE(YEAR(TODAY()),MONTH(TODAY())+2,B2))



Let me know if this works.

Awesome that did the trick, I had the same idea but my similar formula didn't seem to work out properly, thank you!

 

[madhumita]

You are welcome!