Jump to content
By fans, for fans. By fans, for fans. By fans, for fans.

Easy Excel Qu of the day


Sir Tokyo Sexwale

Recommended Posts

I have a lease payment schedule - payments are mid-month.

 

I don't want to type all of the dates, I want a simple formula so I type one & the formula does the rest. But the payments are mid-month, so EOMONTH won't work.

 

So - starting 14 Aug, I then want 14 Sept, 14 Oct etc.

 

14 Aug +30 or +31 isn't going to work. So what will?

Link to comment
Share on other sites

Macca,

 

type 14/08/10 and then 14/09/10 the cell below.

 

highlight them

 

and then drag them.

 

you lazy clunt

despite what everyone else says Al, you're not a complete waste of time.

 

Anyway, I was looking for a 'cool' way of doing it. Not mundane. So if I change the first date, all of the rest change with it

Link to comment
Share on other sites

Up early with the baba, and got bored....use EDATE instead.

 

Problem with it though is if you reference the date in the cell above each time, then you will run into problems if the day of the month is over 28

 

A1 -> 29/01/2010

A2 -> =EDATE(A1,1) (Result: 28/02/2010)

A3 -> =EDATE(A2,1) (Result: 28/03/2010, which is not correct)

A4 -> =EDATE(A3,1) (Result: 28/04/2010, which is not correct)

 

So instead use an absolute reference to the source date instead.

 

A1 -> 29/01/2010

A2 -> =EDATE($A$1,1) (Result: 28/02/2010)

A3 -> =EDATE($A$1,2) (Result: 29/03/2010)

A4 -> =EDATE($A$1,3) (Result: 29/04/2010)

 

and then drag it down. If you then find that the second variable of the EDATE formula doesn't increment, then you need to calculate it instead.

 

A1 -> 29/01/2010

A2 -> =EDATE($A$1,ROW(A2)-ROW($A$1))

A3 -> =EDATE($A$1,ROW(A3)-ROW($A$1))

A4 -> =EDATE($A$1,ROW(A4)-ROW($A$1))

 

ROW(A2)-ROW($A$1) is the row number of the cell minus the row number of the cell containing the source data. A2 calculates to 1, A3 calculates to 2 and so on.

 

If you then decide to drag the column of data to another cell with a different row/column, then the formula works too.

 

File attached with some examples.

 

 

The -ROW($A$1) part of the formula will account for you deciding to start it in a cell without a row reference of 1 (for example: D14)

 

File attached, with some examples.

Book1.zip

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...