datevalue formula

[howard]

I have the following text in A1

Excel 2012
	A
1	Statement of Comrehensive Income as at 30 November 2014
Income Statement

Cell Formulas
Range	Formula
A1	="Statement of Comrehensive Income as at "&TEXT(1*RIGHT(TB!A3,8),"dd mmmm yyyy")

The date appears on sheet "TB" as follows:

Excel 2012
	A
3	Trial Balance : 01/09/14 to 30/11/14
TB

I have the following formula in K1

=DATEVALUE(RIGHT($A$1,15)) The formula returns #value!

However when the date was 31 October 2014 , the formula returns Oct 2014 in K1

It would be appreciated if someone could amend the formula to accommodate all month end dates

 

[barry houdini]

Can you take the date direct from the source, e.g. this formula in K1

=1*RIGHT(TB!A3,8)

 

[oldbrewer]

15 gives you 0 November 2014

 

[howard]

Thanks Barry

Formula works perfectly