As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options
Halp me [EXCEL] - Amortization Table with Dynamic Date Column
Chanus
Harbinger of the Spicy Rooster ApocalypseThe Flames of a Thousand Collapsed StarsRegistered User regular
Harbinger of the Spicy Rooster ApocalypseThe Flames of a Thousand Collapsed StarsRegistered User regular
I made the amortization table no problem, everything works perfectly
except
I want to have a column with the dates of the monthly payments, so like
7/1/2017
8/1/2017
9/1/2017
etc
but I want to be able to change that every time I do a new table, but I don't want to have to enter the date, select the column, fill series every time
I want it to fill the series automatically. So, like, at the top of the column, I type in 7/1/2017 and then there is a formula in the cells all the way down that fills the series by month.
Is that possible? Google has failed me. It's all OH HEY DID YOU KNOW YOU CAN SELECT ALL AND FILL SERIES
Yes, I do know that. I know that so much. That's not what I want.
except
I want to have a column with the dates of the monthly payments, so like
7/1/2017
8/1/2017
9/1/2017
etc
but I want to be able to change that every time I do a new table, but I don't want to have to enter the date, select the column, fill series every time
I want it to fill the series automatically. So, like, at the top of the column, I type in 7/1/2017 and then there is a formula in the cells all the way down that fills the series by month.
Is that possible? Google has failed me. It's all OH HEY DID YOU KNOW YOU CAN SELECT ALL AND FILL SERIES
Yes, I do know that. I know that so much. That's not what I want.
Allegedly a voice of reason.
0
Posts
=A1+IF(OR(MONTH(A1)=1,MONTH(A1)=3,MONTH(A1)=5,MONTH(A1)=7,MONTH(A1)=8,MONTH(A1)=10,MONTH(A1)=12),31,IF(MONTH(A1)=2,28,30))
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Just copy down. It's smart enough that when it gets to being =DATE(2017,13,1) it auto rolls over to 2018, so it works out.
You can do this across rows too. This can also be done with a lookup to create quick self-service graphs for your users, such that you just have all the data on one tab and maintain that, build this out and then have them adjust as they'd like.
If you're really into it, even set validation rules or create a drop-down menu to minimize the odds of them breaking it.