Formatting Data

[howard]

I have a workfile where a have used concatenating , right & Mid functions , but the result shows 9 Months YTD 39234
9 Month YTD 38991

See Attached saple files. The first file shows the incorrect values, the second file has been ranged valued to show what it must look like.

It would be appreciated if you would amend my formulas in the first file and advise

Data1.xls
	A	B	C	D	E	F	G	H
1	CurrentMonthandYear	9MonthsYTD39234	CurrentMonth	June2007	/4	PrevYear	June2006	9
2	PreviousQuarter	9MonthYTD38991	StartPeriod	October2006	2005/	StartPeriodPrevYear	October2005	/
YTD 2007

Book2
	A	B	C	D	E	F	G	H
1	CurrentMonthandYear	9MonthsYTD2007	CurrentMonth	June2007	2006/2007	PrevYear	June2006	9
2	PreviousQuarter	9MonthYTD2006	StartPeriod	October2006	2005/2006	StartPeriodPrevYear	October2005	/
3	TotalNetProfit	TotalNetProfitOctober2006toJune2007	TotalNetProfitOctober2005toJune2006	%Increase/(Decrease)	GroupShare2006/2007	GroupShare2005/2006
Sheet1

 

[Thorin]

The cell D1 is a date reference, replace your reference to D1 with :-

TEXT(D1,"MMMM YYYY")

 

[howard]

Hi Thorin

Thanks for the reply

I need to change the formula in B1 which 9 Months YTD 39234 . It should show 9 Months YTD June 2007

B2 shows 9 Month YTD 38991 9 Months YTD 39234 and should show 9 Months YTD June 2006



The formula in B1 is =IF(H1 = 1, H1&" Month YTD "&(RIGHT(D1,6)&""),H1&" Months YTD "&(RIGHT(D1,6)&""))

The formula in B2 is =IF(H1>3,""&(LEFT(B1,2))&"Month YTD "&(RIGHT(D2,5))&"",""&(LEFT(B1,2))&"Months YTD "&(RIGHT(G2,5))&""

It would be appreciated if you would amend these formula and advise

Howard


See Sample Data attached

Data1.xls
	A	B	C	D	E	F	G	H
1	CurrentMonthandYear	9MonthsYTD39234	CurrentMonth	June2007	/4	PrevYear	June2006	9
2	PreviousQuarter	9MonthYTD38991	StartPeriod	October2006	2005/	StartPeriodPrevYear	October2005	/
YTD 2007

 

[Thorin]

Enter the following in cell B1 :-

=IF(H1 = 1, H1&" Month YTD "&(TEXT(D1,"MMMM YYYY")&""),H1&" Months YTD "&(TEXT(D1,"MMMM YYYY")&""))

Is this what you were after ?

 

[howard]

Hi Andy

Thanks very much for the help. Code working perfectly

Regards

Howard