Club PA 2.0 has arrived! If you'd like to access some extra PA content and help support the forums, check it out at patreon.com/ClubPA
The image size limit has been raised to 1mb! Anything larger than that should be linked to. This is a HARD limit, please do not abuse it.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

Halp me [EXCEL] - Amortization Table with Dynamic Date Column

ChanusChanus Sugoi!^_____^Registered 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.

**Winner Softest and Most Comfy Hugs Award Summer 2018**

Blueberrywerewlf on the Sony Anime Games Box | BluberryWerewlf on the BroBone

Posts

  • Sir LandsharkSir Landshark resting shark face Registered User regular
    Assuming A1 is the cell where you enter the date:

    =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))

    Please consider the environment before printing this post.
    ChanusFeral
  • DaenrisDaenris Registered User regular
    If it's always going to be the same day and you're just incrementing the month, then something like this would work:
    =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.

    ChanusSir LandsharkElvenshaetastydonuts
  • ChanusChanus Sugoi! ^_____^Registered User regular
    i love you guys

    **Winner Softest and Most Comfy Hugs Award Summer 2018**

    Blueberrywerewlf on the Sony Anime Games Box | BluberryWerewlf on the BroBone
  • tastydonutstastydonuts Registered User regular
    Daenris wrote: »
    If it's always going to be the same day and you're just incrementing the month, then something like this would work:
    =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.

    “I used to draw, hard to admit that I used to draw...”
Sign In or Register to comment.