 |
|
| TheExcelAddict.com | |
August 13, 2020
|
|
| Hi fellow Excel Addict, | |
| I'm back from vacation to my daughter's in Nova Scotia. It was such a nice change from the 'bubble' we have been living in for the past five months. The weather was amazing. I got to... ...walk
on the bottom of the ocean at Kingsport Beach.
...escape to beautiful,
breezy Conrad Beach on a very hot and humid day
...and come face to face
with an Egyptian mummy at the Nova Scotia Museum of
Natural History.
 Now I am back in Newfoundland and will enjoy the remainder of the summer. Sorry. I had intended on sending out a newsletter last week but the weather was so hot and our summers are so short, I couldn't drag myself indoors.  If do hope you like today's tip. Please help share my newsletter by share it on your social media accounts — and with your colleagues or any other Excel users you know. Have a great day, keep safe and keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com New Dashboard Course Now
Accepting Enrollment
 |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
| |
|
Having a positive attitude can help us in difficult times. I hope today's quote will help foster a positive attitude in you today. Quote of the Day "If you don’t like something, change it. If you can’t change it, change the way you think about it." -- Mary Engelbreit -- |
|
|
If you have a favourite quote, send it to me
and I may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN SECONDS' TIP |
|
How To Display Dates That Don't Fit In A Column |
|
I'm sure everyone who has used Excel for more than a week has run into this issue at some point. You enter a date in a cell, format it to display the way you want and you see ##### displayed.  The reason why the value in cell A2 displays properly is that 'text' values that are too wide for a column spill across the cells to the right, assuming those cells are empty. However, 'numeric' values (including dates) do not — resulting in those annoying number (#) symbols. Column width solution You may already know that you can fix this simply by making the column wider. However, there may be times, especially with report headings, where you don't want to make the column too wide for the data further below. Merging cells solution? Another option you may already have discovered is to merge the cells (A3 and B3 in the example above) into one cell so that the date fits. Personally, I avoid using merged cells at all costs. Merged cells frequently cause problems with procedures such as selecting, copying, pasting, sorting and filtering. TEXT function solution Instead, the solution that I use in situations like this, is a TEXT function which converts any numeric value to 'text' which WILL span across multiple columns. Here is the syntax of the TEXT function... TEXT(value, format_text)To convert a date to text, you enter a date, or cell reference to a date, in the value section and you enter 'format codes' similar to the ones you use when creating custom number formats in the format_text section. In the case of dates, the codes are obviously y, m and d. In the example above, to enter a date directly in the TEXT function, you will also need to use the DATE(year,month,day) function as the value. For example DATE(2020,09,30) Then, in the 'format codes' section, you enter y, m and d in the format you want your date to appear. To format the date in cell A3 as a 'long date' (e.g. September 30, 2020) that will span across column B, you would use... =TEXT(DATE(2020,09,30),"mmmm d, yyyy")Now that the date has been converted to Text, you will see that it now spans across into column B (assuming the cell in column B is blank).  Alternatively, rather than typing a date directly into the TEXT function, you can instead use a reference to a cell that contains a date. For example, =TEXT(H1,"mmmm d, yyyy") |
|
|
|
|
|
|
|
 |
|
New Dashboard Course Now Accepting Enrollment |
|
|
|
|
 |
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |