how to calculate date base on previous row date value

[bujji]

Respected All,

this is my first post, if i didn't met with guidelines please forgive me

i have a excel sheet

in cell F4 i have a value December 31,2010
in cell F11 i have a value 1
in cell A27 i have formula as =$F$4

in cell A28 the formula is =DATE(YEAR(INDIRECT("R[-1]C",0)),MONTH(INDIRECT("R[-1]C",0))+$F$11,DAY(INDIRECT("R[-1]C",0)))

so here problem is a28 is dragged and used in a29, a30, a31........a 55 (same formula as in a28)

in the mid espeically at feb 30 (since no feb 30 exist) it is missing feb then it is calculating mar 1st ...etc)

could some body assist me how to get add a month




present output
---------------12/31/2008 --> A27
1/31/2009 --> A28
3/3/2009 --> A29
4/3/2009
5/3/2009
6/3/2009
7/3/2009
8/3/2009
9/3/2009
10/3/2009
11/3/2009
12/3/2009
1/3/2010
2/3/2010
3/3/2010 --> A41.



Expected Output is
---------------------12/31/2008 --> A27
1/31/2009 --> A28
2/28/2009 --> A29
3/31/2009
4/30/2009
5/31/2009
6/30/2009
7/31/2009
8/31/2009
9/30/2009
10/31/2009
11/30/2009
12/31/2009
1/31/2010
2/28/2010
3/31/2010 --> A41.


what changes do i need to my formula i am using =DATE(YEAR(INDIRECT("R[-1]C",0)),MONTH(INDIRECT("R[-1]C",0))+$F$11,DAY(INDIRECT("R[-1]C",0)))


also i tried this one but some how it is calculating every month to be as 28....

=DATE(YEAR(INDIRECT("R[-1]C",0)),MONTH(INDIRECT("R[-1]C",0))+$F$11,IF( (MONTH(INDIRECT("R[-1]C",0))+$F$11 ) = 2, IF(MOD(YEAR(INDIRECT("R[-1]C",0)),4)=0,29,28), DAY(INDIRECT("R[-1]C",0))) )


Thanks in advance

Bujji

[martindwilson]

what are you trying to achieve with this?

[bujji]

i attached a file just a work sheet report


in the attachement i highlighted with yello color column

[bujji]

Could somebody please help me?

Thanks a ton in advance

please...


bujj

[royUK]

What good is a pdf for help with a workbook, you need to attach an example workbook

[martindwilson]

try the eomonth() function but im not going any further until you attach an excel version!

[bujji]

Hello Sir,

please find work book i attached,

please see A27, A28 you will get almost good idea what i am try to achieve

Thanks a ton sir,

please help me

workbook.xls

[bujji]

here is workbook i attached

please fins it

[martindwilson]

that works okay it is,you cant pay on 29th of feb so payment moves to march 1 then payments continue at 1 of month, unless you are expecting 2 payments in march of the first and 29th.

[bujji]

Hello Martin,

Thanks for your response,

it is not what it required

instead of feb29 can we show feb28(non leap years) the date will be same in everymonth

for ex if it is dec31 2009, then the expected dates will be Dec 31 2009, Jan31 2009, Feb 28 2009, Mar 31 2009, Apr 30 2009, May 31 2009

so that means if day is not exist in this case(apr 31) then it shold calculate just previous day, please help me

Thanks
Best Regards
Bujji

[Marcol]

Try this
In A27

Please Login or Register  to view this content.

Drag/Fill Down

[bujji]

Hi Marcol,

Excellent, Awsome, i greatful to you Marcol,

it is completelt working what i am expecting except small a small thing which is

on a27 it is calculating next month to F4, it should be same as F4

so what i mean to be is in F4 if we have Dec 31 2008
then A27 will be same which is 12/31/2008 then A28 will be next 01/31/2009 then so on....

right now it is calculating as A28 = F4 values + a monthcould you please change it

Marcol, i greatful to you

Hats offfff

Thanks
bujji

[bujji]

Please help me!!!!

i am almost to end

Thanks a lot
bujji

[Marcol]

In A26

Please Login or Register  to view this content.

Then in A27

Please Login or Register  to view this content.

Drag/Fill Down

[bujji]

Thanks Marcol

Thanks alot

thats work like Charm

i greatful to you!

Thats the solution, oh my goodness Thanks alot

bye for now Marcol

[bujji]

hi marcol can you please remove the file from your last post

please for scurity reasons

thanks

[martindwilson]

now that is daft! anyone reading that will probably download it and open it just to see why. I just did. do not post sensitive info, you could/should have deleted/replaced anything not relevant.

[Marcol]

I have "desensitised" the attachment.
In addition to Martins' comment, you should also remember to remove/change the workbook author in the properties.
If any sensitive information is to be used to your disadvantage, knowing who you are would be quite a handy start.

You might also want to remove the external link you give in Post #7

[martindwilson]

why not just =MIN(EOMONTH(a27,1),EOMONTH(A27,0)+DAY($F$4))

[Marcol]

@ martin .... indeed, why not? ...