Adding number to Text

[howard]

I have the following text on sheet TB

Trial Balance : 01/09/13 to 31/08/14

I have the following formula on sheet "Notes"


="Balance "&TEXT(MID(TB!A3,17,8),"DD MMM YYYY")+29

The result of the formula is Balance 41548

It should read Balance as at 30 September 2013 as I want to extract the ef of month date from 01/09/2014 (format dd/mm/yyyy)

Your assistance in resolving this is most appreciated

 

[Canapone]

Hi,

just made some attempts to check the formula.

="Balance "&TEXT(MID(TB!A3,17,8)+29,"DD MMM YYYY")

Hope it helps

 

[Oeldere]

Probably you better of working with formula eomonth (instead of +29) since not all months have the same end date.

 

[royUK]

It's much easier to have cell with a master date in somewhere , maybe a hidden sheet then refer to that cell. I would name the cell masterDate and use that in formulas.

As suggested EOMONTH is the best way for a dynamic date .

="Balance at " &TEXT(EOMONTH(MID(TB!A3,17,8),0),"DD MMM YYYY")

Your formula has the number of days in the wrong place

="Balance "&TEXT(MID(TB!A3,17,8)+29,"DD MMM YYYY")

 

[howard]

Thanks for the help much appreciated

 

[howard]

Thanks for the help. The EOMONTh formula is great as I do not have to amend each month