# 3 short problems related to dates and cell widths (or workaround)

#### XLS888

##### New Member
Hi All,

Imagine days, months and years running along the top 3 rows. All cells are small squares that can only fit 2 digits in.

Running along row 3 are the days of the month, i.e. date cells (21/08/18) formatted as d

Running along the row 2 are the names of the month on the 1st of each month only by using formula =IF(DAY(A3) = 1,MONTH(A3),0)
0 does not show on other cells because I formatted cells with 0;-0;;@
• Problem 1: It returns a number, not text. I.e. 8 instead of August (mmmm).

If I format cells as mmmm;-0;;@ to show the word then it returns ## because the cell is too small and it does not flow across the next cell, which also has the formula in it (even though the cell shows blank).

• Problem 2: Similarly for the year along row 1, I am using =IF(DAY(A3) = 1,YEAR(A3),0)
But it also shows as ##.

• Problem 3: Ideally I would like the year in full (yyyy) on the 1st day of each quarter,starting with January, not every month.

- It would be easy if I make the cell width wider, but this is not an option.
- Nothing should be entered manually because it needs to extend across many many years.
- Also no code please! I'm a beginner. As very simple/lean as possible would be great.

Many thanks for advice on these.

### 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.

#### Dave Patton

##### Well-known Member

On the actual spreadsheet, "Aug" shows as "Au" because the cell width is short.
What do you want to show for June and July?

Last edited:

#### XLS888

##### New Member
What I am aiming for is to not have Au or Aug, but August in full. Similarly 2018 in full (and for the year ideally only every quarter, not monthly)

If it's written in plain text, no problem because it goes across cells. But formulae in every cell, it is cut off short as we both mention. I probably described it all better in the original post above.

Is there a workaround? Thanks! :/

#### Dave Patton

##### Well-known Member

Cells B1:C2 are blank; on the actual sheet A1 and A2 flow into Column B

Last edited:

#### XLS888

##### New Member
Thanks, but I'm not sure you ever read my post properly

It would be easy if I make the cell width wider, but this is not an option.

But I really do appreciate your efforts, and indeed anyone that can help.

#### XLS888

##### New Member
Perhaps what I could do is merge cells A2 and B2 and then ask IF A3:B3 has DAY=1 then show TEXT(A3,"mmmm")

- Could you construct this formula?
- Does this seem a sensible solution?

Many thanks

Last edited:

Replies
3
Views
499
Replies
3
Views
90
Replies
7
Views
531
Replies
0
Views
345
Replies
0
Views
157

1,148,334
Messages
5,746,162
Members
423,997
Latest member
moofish87

### 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