Amazon affiliate link: Hubby hit me with a request for a Gantt chart in Excel this week, which is OK. I can colour the background of cells with the best of them! But then I decided to turn this into a project where you get Excel and OpenOffice templates to use, free! As I was putting it together, however, I was starting to wonder if there was a way that I could automate the creation of the bars based on Conditional Formatting.

1. Libre Calc Drop Down Menu

I’m sure I could have Google’d it and come up with a spreadsheet, however this way I ended up with:. An Excel Spreadsheet that works on versions of Excel that don’t support the NetworkDays Function (before 2007 I believe). Fs2004 constellation download. An Excel Spreadsheet that uses the NetworkDays Function.

In part 2 the actual chart is created using conditional formatting. The NETWORKDAYS.INTL function is.

And an OpenOffice version Even though this site has historically been all about OpenOffice, I figured I may as well share the love on the Excel versions as well, as DH’s company uses Excel, and it was the foundation for the OpenOffice version. I am using Openoffice 3.2.0. Now I’m not going to go through how I put this together. What I will cover in this outline is as follows:. Date formatting – you can format this spreadsheet either by the days of the week (a hidden row on this spreadsheet), or by weeks (as displayed above).

I’ll show you how to hide and show rows. Conditional Formatting – how and where it is used on this spreadsheet to create the bars of the Gantt chart. How the number of days in the project are calculated – given that we are only counting working days), and how I’ve used 2 different methods to do this. Presentation – Days of the Week or By Week? Originally I set this up so every day of the week was dated across the top of the Gantt Chart.

Dear Husband (DH) said that this was too cluttered so we decided to use the ‘Week Commencing’ title and settle on the Monday date as the date that defines the week. You can see that I’ve hidden a row in the spreadsheet – Row 3 in the example below – which has all the dates from Monday to Friday (however the columns are very narrow to allow for more weeks to fit on the page). I didn’t delete this row even though I chose to display on a weekly basis as I use it for calculating the format for the bars – see the section below ‘Putting the Bars In’. Start Date The first date in the chart is best calculated as the first date of the project dates, so in cell E3, I’ve used the MIN function to calculate the earliest date out of all the dates in column C. Note: I would ideally make this always a Monday. The spreadsheet would go wonky if it wasn’t.

Something to consider for a future revision – i.e. Calculate the first Monday before the earliest date, if the earliest date is not on a Monday. Calculating Days – Method 1: NetworkDays There are a number of day calculations that are necessary to show the start and end dates of tasks, of activities, and to calculate the number of working days it takes to complete a task or activity. Just to be clear, I’m using the terms activities and tasks, meaning that an Activity (like Perth (WA) in the figure below) is made up of a subset of tasks. Here is a view of one set of tasks and activities, with the forumlae shown: From the above, we can see:. Start dates should be before end dates (logical – but check this as it can throw your spreadsheet if you accidentally put in the wrong year, for example). The min of all the task start dates should indicate the start date for the whole activity.

The max of all the task end dates should indicate the end date for the whole activity. NetworkDays can be used to calculate the number of working days between the start date and end date. Note that NetworkDays is not available in all versions of OpenOffice or Excel. Note: Another future improvement I intend to add is to include a list of holidays in a range outside of the Gantt chart, which NetworkDays can refer to ensure that it doesn’t include these days in the calculation. For this spreadsheet, DH wasn’t concerned with this, so it wasn’t something I included.

Calculating Days – Method 2: SumIf This will make more sense after you read the section below, so I’ll continue it after Putting the Bars in – Conditional Formatting Now I probably could have done this without the 1 and zero calculations below, however because the NetworkDays formula didn’t work for DH’s version of Excel, this turned out to be quite useful. For each cell I took the individual date value in (hidden) Row 3, and I compared it to the start date and finished date. If it satisfied both of these tests, then it would return a one – meaning that it fell on or between the date periods. If it failed one or both of these tests, it would return a zero and mean that it fell outside of the date period.

See the figure below for an example. So all that was left for me to do was to test each date in row 3 against the task start and end dates, and return a 1 or 0 to the cell. Then I could tell the spreadsheet to format the cell depending on the value returned. This resulted in bars. If you are not sure about conditional formatting, check out. I added a layer of complexity for this by having different background colouring for activities (green) and tasks (grey). I cut and paste this manually, but maybe another iteration of this spreadsheet will have something where this is worked out automatically.

Calculating Days – Method 2: SumIf (continued) So in an earlier version of Excel or OpenOffice where NetworkDays doesn’t work, the above 1 and 0 method for formatting allowed a really easy way for me to calculate the number of days duration for a project (Column B). You can see this in the figure below: And that’s pretty much all there is to it! I tidied it up with a little formatting (made some of the rows quite narrow), but other than that, it’s your basic gantt chart. I have protected the cells so that I don’t accidentally edit them, but you can remove the cell protection by:. In OpenOffice, select Tools – Protect Document – Sheet (no password). In Excel, select Tools – Protection – Unprotect Sheet (no password) Please suggest any improvements in the comments! Download To download the file, you will need to be a registered member of this site, due to spammers and the like stealing my bandwidth.

It’s free, and other than the occasional email (less than once a month), you won’t be harrassed by me! If you are not already subscribed, please.

Libre Calc Drop Down Menu

Who is behind Guide2Office? My name is Stephanie Krishnan and I'm passionate about the way that open source software and its community can help small businesses and individuals with their productivity and lives. One of the biggest arguments I get from business owners, however, is lack of support options. I decided to put together my own support blog to help people be productive at various levels with various Office software, including OpenOffice.org, LibreOffice, NeoOffice, MS Office and Apple products!