Solved: Help Date's

[CCkfm2000]

Hello Excel Guru's


In cell C7 we have a date 31-Mar-2012 I need a formula to check if the date is the end of month and take a year off but add a day on so it should day 01-Apr-2011


If any other date in cell C7 e.g 28-Mar-2011 I need a formula to take a year off and add a day on so it should day 27-Mar-2011


Thanks

[Bob Phillips]

Try

=IF(MONTH(A2)<>MONTH(A2+1),DATE(YEAR(A2)-1,MONTH(A2+1),1),A2-1)

[CCkfm2000]

Thanks xld, the first part work.


sorry made a mistake with the 2nd part

If any other date in cell C7 e.g 28-Mar-2011 I need a formula to take a year off and add a day on so it should day 27-Mar-2010

[Bob Phillips]

Then that would be

=IF(MONTH(A2)<>MONTH(A2+1),DATE(YEAR(A2)-1,MONTH(A2+1),1),DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)-1))

[CCkfm2000]

Thanks thats great