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

XLS888

New Member
Joined
Sep 30, 2017
Messages
9
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
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Aug</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2a</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">A3</font>) = 1,TEXT(<font color="Red">A3,"yy"</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">A3</font>) = 1,TEXT(<font color="Red">A3,"mmm"</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

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
Joined
Sep 30, 2017
Messages
9
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
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Aug</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">3a</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">A3</font>) = 1,TEXT(<font color="Red">A3,"yyy"</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">A3</font>) = 1,TEXT(<font color="Red">A3,"mmm"</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

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

XLS888

New Member
Joined
Sep 30, 2017
Messages
9
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
Joined
Sep 30, 2017
Messages
9
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:

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top