Nel post
*Franz Verga* ha scritto:

Nel post
news:CarolineHedges.29un1b_1151056202.6671@excelfo rum-nospam.com
*CarolineHedges* ha scritto:
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


Hi Caroline,

maybe (quite sure... ;-) ), you didn't tried this one:

="Nav at "&TEXT(EOMONTH(D3,-1),"dd mmmm yyyy")

where D3 is the cell in which there is the date, so you have to
arrange the reference as you need. Also "dd mmmm yyyy" is the
formatting code for the date, you have to arrange this too as for
your need.

Because the EOMONTH function need the Analysis Tool Pack (ATP) installed,
you may also prefer to change the previous formula with this one:

="Nav at "&TEXT(DATE(YEAR(D3),MONTH(D3),0),"gg mmmm aaaa")

which don't need ATP.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy