Related:

- Recurring date formula excel
- Recurring dates in excel - Best answers
- Recurring formula in excel - Best answers
- If formula excel date range - How-To - Excel
- If cell color is then formula excel - How-To - Excel
- Filter formula excel ✓ - Forum - Excel
- Debit credit balance formula excel - How-To - Excel
- Grade formula excel - How-To - Excel

TrowaD

- Posts
- 2448
- Registration date
- Sunday September 12, 2010
- Status
- Contributor
- Last seen
- February 14, 2019

Hi MRafik,

Then I need to know when you want to add x numbers of days.

Let's say your start date is 31-1-2013.

And you choose option 3; Monthly.

Your formula would show as result 28-2-2013.

What is the result you would like to see?

1-3-2013 (+29), 2-3-2013 (+30) or 3-3-2013 (+31).

And what result would you like to see when start date is 28-2-2013?

And what if start date is 31-3-2013?

Don't you always want to add 31 when option 3 is chosen?

As you can see it is not very clear to me what result you are after.

So please provide some results your formula should give you with certain start dates.

Best regards,

Trowa

Then I need to know when you want to add x numbers of days.

Let's say your start date is 31-1-2013.

And you choose option 3; Monthly.

Your formula would show as result 28-2-2013.

What is the result you would like to see?

1-3-2013 (+29), 2-3-2013 (+30) or 3-3-2013 (+31).

And what result would you like to see when start date is 28-2-2013?

And what if start date is 31-3-2013?

Don't you always want to add 31 when option 3 is chosen?

As you can see it is not very clear to me what result you are after.

So please provide some results your formula should give you with certain start dates.

Best regards,

Trowa

Hi Trowa

Thanks for the reply

My work sheet is for rents due so if the rent due is on the 1st

of each month then the cell B8 will be 01/01/2013 and B9 will

be 01/02/2013 and so on. So if start date is 29/01/2013 then the next cell

should be 28/02/2013(except when it is a leap year) and the next

should be 29/03/2013.

If the start date is 30/01/2013 then the next should be 28/02/2013

(except when it is a leap year) and the next should be 30/03/2013

etc.

If start date is 31/01/2013 then it should be 28/02/2013(except when it is a leap year)and the next one should be 31/03/2013 and the next 30/04/2013 ie the last

day of each month.

I tried the following IF($AA$2=3,DATE(YEAR(B8),MONTH(B8)+1,DAY(B8) for

option 3 but it does not work.

Please note my options are in a combo box .

Rgards

Thanks for the reply

My work sheet is for rents due so if the rent due is on the 1st

of each month then the cell B8 will be 01/01/2013 and B9 will

be 01/02/2013 and so on. So if start date is 29/01/2013 then the next cell

should be 28/02/2013(except when it is a leap year) and the next

should be 29/03/2013.

If the start date is 30/01/2013 then the next should be 28/02/2013

(except when it is a leap year) and the next should be 30/03/2013

etc.

If start date is 31/01/2013 then it should be 28/02/2013(except when it is a leap year)and the next one should be 31/03/2013 and the next 30/04/2013 ie the last

day of each month.

I tried the following IF($AA$2=3,DATE(YEAR(B8),MONTH(B8)+1,DAY(B8) for

option 3 but it does not work.

Please note my options are in a combo box .

Rgards

TrowaD

- Posts
- 2448
- Registration date
- Sunday September 12, 2010
- Status
- Contributor
- Last seen
- February 14, 2019

Hi MRafik,

The problem was that your reference to the start date was relative.

Meaning that:

1 month + 31-1-2013 (B8) = 28-2-2013 (B9)

1 month + 28-2-2013 (B9) = 28-3-2013 (B10)

Making the reference absolute was the first step, but then we need something to add months.

Otherwise we would get:

1 month + 31-1-2013 (B8) = 28-2-2013 (B9)

1 month + 31-1-2013 (B8) = 28-3-2013 (B10)

[ CELL("row",B9)-8 ] is just used as a counter.

[ CELL("row",B9) ] will result 9 as is the row number of B9.

Deduct 8 from 9 = 1 or 1 month.

For the next row the result will be 10 as is the row number of B10.

Deduct 8 from 10 = 2 or 2 months.

and so on...

So for monthly the counter would need to add 1.

But for Quarterly the counter would need to add 3.

So think about how you can get 3, 6, 9 ...

Solution:

Use row of B1 = 1, times 3 = 3

Next cell would be B2 = 2, times 3 = 6

Thus part of formula would be:

IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)

Now that I think about it a second time this basically solves it all, right?

Monthly: IF($AA$2=3,EDATE(B$8,CELL("row",B1)

Quarterly: IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)

Half Yearly: IF($AA$2=6,EDATE(B$8,CELL("row",B1)*6)

Annually: IF($AA$2=7,EDATE(B$8,CELL("row",B1)*12)

Best regards,

Trowa

The problem was that your reference to the start date was relative.

Meaning that:

1 month + 31-1-2013 (B8) = 28-2-2013 (B9)

1 month + 28-2-2013 (B9) = 28-3-2013 (B10)

Making the reference absolute was the first step, but then we need something to add months.

Otherwise we would get:

1 month + 31-1-2013 (B8) = 28-2-2013 (B9)

1 month + 31-1-2013 (B8) = 28-3-2013 (B10)

[ CELL("row",B9)-8 ] is just used as a counter.

[ CELL("row",B9) ] will result 9 as is the row number of B9.

Deduct 8 from 9 = 1 or 1 month.

For the next row the result will be 10 as is the row number of B10.

Deduct 8 from 10 = 2 or 2 months.

and so on...

So for monthly the counter would need to add 1.

But for Quarterly the counter would need to add 3.

So think about how you can get 3, 6, 9 ...

Solution:

Use row of B1 = 1, times 3 = 3

Next cell would be B2 = 2, times 3 = 6

Thus part of formula would be:

IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)

Now that I think about it a second time this basically solves it all, right?

Monthly: IF($AA$2=3,EDATE(B$8,CELL("row",B1)

Quarterly: IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)

Half Yearly: IF($AA$2=6,EDATE(B$8,CELL("row",B1)*6)

Annually: IF($AA$2=7,EDATE(B$8,CELL("row",B1)*12)

Best regards,

Trowa