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

[XLS888]

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.

 

[Dave Patton]

Excel 2010
	A
1	18
2	Aug
3	1
2a

Cell Formulas
Range	Formula
A1	=IF(DAY(A3) = 1,TEXT(A3,"yy"),"")
A2	=IF(DAY(A3) = 1,TEXT(A3,"mmm"),"")

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

 

[XLS888]

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]

Excel 2010
	A	B	C
1	2018
2	Aug
3	1	2	3
3a

Cell Formulas
Range	Formula
A1	=IF(DAY(A3) = 1,TEXT(A3,"yyy"),"")
A2	=IF(DAY(A3) = 1,TEXT(A3,"mmm"),"")

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

 

[XLS888]

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]

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