98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Charts
Charts
Resolved · Medium Priority · Version 2016
Thu 1 Aug 2019, 09:45Delegate Franco said...
Franco has attended:
Excel Intermediate course
Mentoring course
Charts
How do I create a bar chart from a series of dates please?
Thu 1 Aug 2019, 19:10Trainer Jens said...
RE: charts
Hi Franco,
Thank you for the forum question.
It should be straight forward to create a chart from a series of dates, but since you ask the question I believe that I really do not understand what you like to achieve.
I need more information to be able to help you. You can send me a dummy worksheet, so I can see how your data are organised or please explain in more details here, how your data are organised and how you want you chart to display the date series thanks.
If you want to send an example:
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 2 Aug 2019, 09:19Delegate Franco said...
RE: charts
Hi Jens,
Thanks for getting back to me.
I really enjoyed our course together so thank you.
As you know, we are a property company and part of what we do is letting out residential property (flats and houses).
These are typically let for periods of 12 months at a time but some are longer. I would like to know when the tenancies expire in a bar chart format so I can see where the spikes are.
The concern is that if we have too many tenancies ending in in a month that is heavy (and the tenants choose not to renew their tenancy) then we could suffer a big loss of rental income in those busy months.
If we know where the spikes are, we could try and reduce them by encouraging tenants to take longer than 12 months, 13 - 16 months so as to even out the spikes for the following year and have a less dramatic bar chart profile.
I hope this makes sense. I have a table I could send you by way of example but I am not sure how I attach it to this message.
Thank you in advance for your help.
Kind regards,
Franco
Fri 2 Aug 2019, 14:49Trainer Jens said...
RE: charts
Hi Franco,
I am happy you enjoyed the course.
I will be easier for me to give you the right answer if I can see the table.
Please send it to:
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 2 Aug 2019, 15:57Trainer Jens said...
RE: charts
Hi Franco,
The problem is that you do not have dates in the table. It looks like dates but it is text.
You also have "vacant", "on-going","09-Feb-20120", "12-Oct-20189",
"under offer" in the AST column.
Excel does not like this.
Please find the attachment (if you cannot see the attachment please wait 5 minutes and refresh the browser).
I have added a new column to extract the date from the ast expire column, but because of the text entries the datevalue function returns error. I sorted the the convert to date column ascending to get the error at the bottom.
I created a Pivot Table from the range A1:H782. From the Pivot Table I created the chart.
I hope this makes sense otherwise let me know.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Copy of Table for Jens 2 Auig 2019.xlsxFri 2 Aug 2019, 16:22Delegate Franco said...
RE: charts
Hi Jens,
I thought there was a problem with the dates being text and I tried to correct this but did not succeed.
I haven't received the attachment you sent but I will keep looking.
Thank you again for your help and I look forward to seeing the chart so that I can see how you managed to produce it.
Kind regards,
Franco
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Adding up time greater than 24 hoursWhen you add up time if it exceeds 24 hours i.e 27 hours appears as 03:00. Go to Format / Cells / Number / Custom. The format is hh:mm but if change it to [hh]:mm it will add up to the correct amount of hours. |
