# Formatting Data

#### howard

##### Well-known Member
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
ABCDEFGH
1CurrentMonthandYear9MonthsYTD39234CurrentMonthJune2007/4PrevYearJune20069
2PreviousQuarter9MonthYTD38991StartPeriodOctober20062005/StartPeriodPrevYearOctober2005/
YTD 2007
Book2
ABCDEFGH
1CurrentMonthandYear9MonthsYTD2007CurrentMonthJune20072006/2007PrevYearJune20069
2PreviousQuarter9MonthYTD2006StartPeriodOctober20062005/2006StartPeriodPrevYearOctober2005/
3TotalNetProfitTotalNetProfitOctober2006toJune2007TotalNetProfitOctober2005toJune2006%Increase/(Decrease)GroupShare2006/2007GroupShare2005/2006
Sheet1

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Thorin

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

Code:
``TEXT(D1,"MMMM YYYY")``

#### howard

##### Well-known Member
Hi Thorin

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
ABCDEFGH
1CurrentMonthandYear9MonthsYTD39234CurrentMonthJune2007/4PrevYearJune20069
2PreviousQuarter9MonthYTD38991StartPeriodOctober20062005/StartPeriodPrevYearOctober2005/
YTD 2007

#### Thorin

##### Board Regular
Enter the following in cell B1 :-

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

Is this what you were after ?

#### howard

##### Well-known Member
Hi Andy

Thanks very much for the help. Code working perfectly

Regards

Howard

Replies
4
Views
159
Replies
0
Views
102
Replies
6
Views
127
Replies
4
Views
400
Replies
11
Views
384

1,191,092
Messages
5,984,587
Members
439,896
Latest member
SquareCare

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back