=IF(DAY(A1)DAY(DATE(YEAR(A1),MONTH(A1),0)),DATE(Y EAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)+1))

HTH
--
AP

"Max" a écrit dans le message de news:
...
it doesn't fully work..

Yes, but that was the stated presumption / caveat in the earlier response
..

Try instead in B1, copied down:
=IF(DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)=DATE(YEAR(A 1),MONTH(A1)+1,1),DATE(YEAR(A1),MONTH(A1),1),DATE( YEAR(A1),MONTH(A1)-1,DAY(A1)+1))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wfactor" wrote:

it doesn't fully work..

march 28 - 31
if the provided date is the 31st, and the month prior doesn't have 31
days then it also causes the wrong new date to show

provided date your formula date
4/01/2007 5/12/2006 (correct)
1/01/2007 2/12/2006 (correct)
31/03/2007 4/03/2007 (should be 01/03/07)
31/12/2006 2/12/2006 (should be 01/12/06)


--
wfactor
------------------------------------------------------------------------
wfactor's Profile:
http://www.excelforum.com/member.php...o&userid=35548
View this thread:
http://www.excelforum.com/showthread...hreadid=553104