If A1 contains 30/06/2006 then:

="Nav as at " & TEXT(DATE(YEAR(A1),MONTH(A1),0),"dd mmmm yy")

will display "Nav as at 31 May 06"

Does this help?


"CarolineHedges" wrote:


I am trying to write a formula which has text and then a date which is
taken from a different sheet in the workbook.

EG the text is "Nav as at 31st March 06"

With 'Nav as at' being the text and the date 31st March being picked up
from the other sheet.

However the date on the other sheet is the following month, ie 30th
June. SO I would need it to pick up the End of the month and minus it
by one month and add it to the text.

I have tried several methods but none seem to work!!

Thanks

Caroline


--
CarolineHedges
------------------------------------------------------------------------
CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705
View this thread: http://www.excelforum.com/showthread...hreadid=554904