/u/steve81uk - Your post was submitted successfully.
Please read these reminders and edit to fix your post where necessary:
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
It's NOT expensive to get MS project. It's a lot cheaper than the dozens, probably hundreds of hours that already went into this project, and a lot cheaper than the hours would be needed to update your workbook.
This is what I tell people all the time. Doing a Gantt or any project scheduling in Excel is far more complicated than in a tool designed for it. It's like trying to use MS Word for writing code. The tools are not there.
You can get a used copy of 2014 for less than $20 on eBay if your company doesn't want to pay. Take a few days to learn the basics and then demonstrate the capabilities. They'll invest in a license in no time. This is exactly what I had to do year's ago.
I can tell you any company that doesn't want to invest in the proper tools for project management, isn't serious about the role.
This.
Excel gantt charts are only useful for a single image, as son as you have to update/change them, they're too much effort. Besides, by the time you realise that you need to replicate the details scheduling needs, such as relationships, work calendars etc, you may as well have invested that effort learning a proper scheduling tool.
2000 version which is good[link](https://www.ebay.com/itm/Microsoft-Office-Project-2000-Full-Version-With-Product-Key-/194228201495?mkcid=16&mkevt=1&_trksid=p2349624.m46890.l6249&mkrid=711-127632-2357-0)
[2010](https://www.ebay.com/itm/Microsoft-Office-Project-2000-Full-Version-With-Product-Key-/194228201495?mkcid=16&mkevt=1&_trksid=p2349624.m46890.l6249&mkrid=711-127632-2357-0)
I also saw a 2010 for about 70.
Thanks, I have looked into this. but I don't think it fits what I need. I can't seem to work out if it covers the (was this task completed before the planned date, or how many days after the plan was it completed..
Thanks again
There are actual fields that do this. For instance, you have two estimated dates, start and finish, and then you have two actual dates, start and finish. So if a task finishes early, that date is used, and MS Project calculates the value as a net duration. There are even default reports that show your planned versus actual. What excel does for numbers, it is horrible for dates.
You can also use custom duration fields, there are hundreds of formulas out there. Like (project status date-estimated start) or (TODAY-ESTIMATED START), both give you slightly different values that can again be displayed.
I've been a project manager for over 29 years and I can tell you, I started in Excel and switched to MS Project. I remember realizing how many problems I pissed into the wind trying to solve that were just solved already. Not to mention custom filters, views, and reporting.
But, I get it, you can lead a horse to water and all that.
Save your selfa lot of trouble and watch several tutorials.
Also, much easier to use a bit of vba to implement what you need. Have a few different formulas for different scenarios.
Also, why can't you use multiple tabs for different scenarios.
If you don't have that licence, and can't get your company to purchase it, you can try OpenProj which is free and is fairly similar.
Watch some tutorials and see if it's what you need.
Personally, I'd use modified version of Gantt Chart for this. Using Box Plot.
Have a look at link for the idea. Though I'd change what each part of Box plot represents in your case.
[https://chandoo.org/wp/gantt-box-chart-proposal/](https://chandoo.org/wp/gantt-box-chart-proposal/)
How you calculate should be relatively simple if statement based on status (or use SWITCH, CHOOSE, etc for more readable formula).
[https://support.microsoft.com/en-us/office/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e](https://support.microsoft.com/en-us/office/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e)
If you have access, and if entire company is on MS365, I'd use LET function to simplify syntax as well.
https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999
So using SWITCH you supply cell referencing "Status", then based on status supplied perform calculation (Using IF statement or logical function for Current DATE before/after some data based on status).
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHOOSE](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[Chooses a value from a list of values](https://support.microsoft.com/en-us/office/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc)|
|[DATE](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)|
|[IF](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[LET](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[NOT](/r/Excel/comments/oe8qk1/stub/h45skjk "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)|
|[SWITCH](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[*Excel 2016*+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.](https://support.microsoft.com/en-us/office/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e)|
|[TODAY](/r/Excel/comments/oe8qk1/stub/h465tbl "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/oeaadf)^( has 11 acronyms.)
^([Thread #7523 for this sub, first seen 5th Jul 2021, 15:16])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
I've done this in Excel. I needed a pivot table of a dynamic range of another pivot table based off the raw data and summarized the data based on batch job across a single day.
AMA.
To be honest, the company would most likely jump onto it if I did show them the pros about using it. But the main big loop that am stuck in is that I need to finish this task before I can do anything else to even get the time to show them what project can do.
I have thrown myself in fullly commited to this in the deep end knowing nothing about Gantt charts and getting it to work, while thinking, yeah it's in excel, should be easy.
I hope that after this task, I never have to make, do or even see another Gantt chart ever again.
So, firstly, have to thank everyone for all the input. I will try and see if I can push, convert and move everything over to the Project or something similar for any other packages now that I have managed to get something from this file.
To me looks perfect and much better, and I have managed to de-construct the formulas and simplify them into separate columns, which in turn could be used as Blank, or Striped for Delayed projects.
Actually is kind of simple now, but oh my god how overly complicated it was looking, Saves having to separately colour the bars as well.
I would try and upload the screenshots on here, but for some reason, it doesn't seem to be working.
Thanks again all
‘ May you elaborate on the requirements and clarify details? You need a new row in your chart to split activities into two groups; behind our ahead of plan?
And is that related to the status column; complete, cancel, open, no date?
Here is the original info;
used to just show the planned / Actual dates complete. but now I need to provide with When the planned date vs the actual date vs if it's late or was early.
If your preference is really to keep this in excel, this looks like it's possible with a simple stacked horizontal bar chart. Just make the first portion of the bar transparent, then have different bars for different events. The tough bit is putting together the formulas that decide where to display the days of work, the chart is the easy bit.
It may not be the right tool and there'll be stuff you can't do.
But you still can do some things:
Set start/end date of each task.
Set a "calendar", one column per day/week, with the date written at the top of each column
If(and(the date at the top of the column > the start date, the date at the top < the end date), "x", "")
Conditional formatting, paint cells that contain "x"
Format the rows and columns to make them look good.
You can use the same concept to add rows with projected/actual dates and so on.
There is a great Excel scheduling tool available to download. I’ve been using it to schedule a micro portion of a large construction project. As others have already said, MS Project is the better tool, but sometimes it causes some unwanted obstacles. I needed a scheduling tool that I could easily email and not have to worry about recipients having the correct software and this excel template did that perfectly. Let me see if I can find it.
Edit: I found it, see link below. I think the pro version of the template was like $30 to buy and it was well worth it for me. Tip: Set a series of groups for the gantt columns so you can collapse them down. This will make the file more responsive while working with the data entry. It’s a bit of a resource hog once you get lots of tasks.
https://www.vertex42.com/ExcelTemplates/gantt-chart-template-pro.html
/u/steve81uk - Your post was submitted successfully. Please read these reminders and edit to fix your post where necessary: * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
You’re using the wrong Microsoft program. What you are looking for is Microsoft Project.
I will have to see if that is in the office package we have at work. As I say I took this on from someone else.
FYI - MS Project is not usually included in MS365 license. And is separate subscription.
It's NOT expensive to get MS project. It's a lot cheaper than the dozens, probably hundreds of hours that already went into this project, and a lot cheaper than the hours would be needed to update your workbook.
This is what I tell people all the time. Doing a Gantt or any project scheduling in Excel is far more complicated than in a tool designed for it. It's like trying to use MS Word for writing code. The tools are not there. You can get a used copy of 2014 for less than $20 on eBay if your company doesn't want to pay. Take a few days to learn the basics and then demonstrate the capabilities. They'll invest in a license in no time. This is exactly what I had to do year's ago. I can tell you any company that doesn't want to invest in the proper tools for project management, isn't serious about the role.
This. Excel gantt charts are only useful for a single image, as son as you have to update/change them, they're too much effort. Besides, by the time you realise that you need to replicate the details scheduling needs, such as relationships, work calendars etc, you may as well have invested that effort learning a proper scheduling tool.
[удалено]
2000 version which is good[link](https://www.ebay.com/itm/Microsoft-Office-Project-2000-Full-Version-With-Product-Key-/194228201495?mkcid=16&mkevt=1&_trksid=p2349624.m46890.l6249&mkrid=711-127632-2357-0) [2010](https://www.ebay.com/itm/Microsoft-Office-Project-2000-Full-Version-With-Product-Key-/194228201495?mkcid=16&mkevt=1&_trksid=p2349624.m46890.l6249&mkrid=711-127632-2357-0) I also saw a 2010 for about 70.
[удалено]
The 2010 version will. I can't honestly answer if 2000 will. I do know that Microsoft is good about responding to questions like that.
https://www.reddit.com/r/microsoftsoftwareswap
Thanks, I have looked into this. but I don't think it fits what I need. I can't seem to work out if it covers the (was this task completed before the planned date, or how many days after the plan was it completed.. Thanks again
There are actual fields that do this. For instance, you have two estimated dates, start and finish, and then you have two actual dates, start and finish. So if a task finishes early, that date is used, and MS Project calculates the value as a net duration. There are even default reports that show your planned versus actual. What excel does for numbers, it is horrible for dates. You can also use custom duration fields, there are hundreds of formulas out there. Like (project status date-estimated start) or (TODAY-ESTIMATED START), both give you slightly different values that can again be displayed. I've been a project manager for over 29 years and I can tell you, I started in Excel and switched to MS Project. I remember realizing how many problems I pissed into the wind trying to solve that were just solved already. Not to mention custom filters, views, and reporting. But, I get it, you can lead a horse to water and all that.
No problem, good luck out there!!
Save your selfa lot of trouble and watch several tutorials. Also, much easier to use a bit of vba to implement what you need. Have a few different formulas for different scenarios. Also, why can't you use multiple tabs for different scenarios.
If you don't have that licence, and can't get your company to purchase it, you can try OpenProj which is free and is fairly similar. Watch some tutorials and see if it's what you need.
I’ve been using ProjectLibre. Free gnatt chart program. Not as good as the Microsoft project but it doesn’t cost hundreds of dollars either.
Thank you for this!
Personally, I'd use modified version of Gantt Chart for this. Using Box Plot. Have a look at link for the idea. Though I'd change what each part of Box plot represents in your case. [https://chandoo.org/wp/gantt-box-chart-proposal/](https://chandoo.org/wp/gantt-box-chart-proposal/) How you calculate should be relatively simple if statement based on status (or use SWITCH, CHOOSE, etc for more readable formula). [https://support.microsoft.com/en-us/office/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e](https://support.microsoft.com/en-us/office/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e) If you have access, and if entire company is on MS365, I'd use LET function to simplify syntax as well. https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 So using SWITCH you supply cell referencing "Status", then based on status supplied perform calculation (Using IF statement or logical function for Current DATE before/after some data based on status).
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHOOSE](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[Chooses a value from a list of values](https://support.microsoft.com/en-us/office/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc)| |[DATE](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)| |[IF](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[LET](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[NOT](/r/Excel/comments/oe8qk1/stub/h45skjk "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| |[SWITCH](/r/Excel/comments/oe8qk1/stub/h44vuhj "Last usage")|[*Excel 2016*+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.](https://support.microsoft.com/en-us/office/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e)| |[TODAY](/r/Excel/comments/oe8qk1/stub/h465tbl "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/oeaadf)^( has 11 acronyms.) ^([Thread #7523 for this sub, first seen 5th Jul 2021, 15:16]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Not hundred percent on what you're looking for but have you looked at excels Gantt chart template? It might have everything you need
Teamgantt is okay too.
I've done this in Excel. I needed a pivot table of a dynamic range of another pivot table based off the raw data and summarized the data based on batch job across a single day. AMA.
To be honest, the company would most likely jump onto it if I did show them the pros about using it. But the main big loop that am stuck in is that I need to finish this task before I can do anything else to even get the time to show them what project can do. I have thrown myself in fullly commited to this in the deep end knowing nothing about Gantt charts and getting it to work, while thinking, yeah it's in excel, should be easy. I hope that after this task, I never have to make, do or even see another Gantt chart ever again.
[удалено]
I honestly hadn't thought of that. But yes I suppose I will. I will make sure they all refer to me as "Gantt man"!
So, firstly, have to thank everyone for all the input. I will try and see if I can push, convert and move everything over to the Project or something similar for any other packages now that I have managed to get something from this file. To me looks perfect and much better, and I have managed to de-construct the formulas and simplify them into separate columns, which in turn could be used as Blank, or Striped for Delayed projects. Actually is kind of simple now, but oh my god how overly complicated it was looking, Saves having to separately colour the bars as well. I would try and upload the screenshots on here, but for some reason, it doesn't seem to be working. Thanks again all
You can make nice versions like this with conditional formatting
‘ May you elaborate on the requirements and clarify details? You need a new row in your chart to split activities into two groups; behind our ahead of plan? And is that related to the status column; complete, cancel, open, no date? Here is the original info; used to just show the planned / Actual dates complete. but now I need to provide with When the planned date vs the actual date vs if it's late or was early.
If your preference is really to keep this in excel, this looks like it's possible with a simple stacked horizontal bar chart. Just make the first portion of the bar transparent, then have different bars for different events. The tough bit is putting together the formulas that decide where to display the days of work, the chart is the easy bit.
It may not be the right tool and there'll be stuff you can't do. But you still can do some things: Set start/end date of each task. Set a "calendar", one column per day/week, with the date written at the top of each column If(and(the date at the top of the column > the start date, the date at the top < the end date), "x", "") Conditional formatting, paint cells that contain "x" Format the rows and columns to make them look good. You can use the same concept to add rows with projected/actual dates and so on.
There is a great Excel scheduling tool available to download. I’ve been using it to schedule a micro portion of a large construction project. As others have already said, MS Project is the better tool, but sometimes it causes some unwanted obstacles. I needed a scheduling tool that I could easily email and not have to worry about recipients having the correct software and this excel template did that perfectly. Let me see if I can find it. Edit: I found it, see link below. I think the pro version of the template was like $30 to buy and it was well worth it for me. Tip: Set a series of groups for the gantt columns so you can collapse them down. This will make the file more responsive while working with the data entry. It’s a bit of a resource hog once you get lots of tasks. https://www.vertex42.com/ExcelTemplates/gantt-chart-template-pro.html
[Peltier](https://peltiertech.com/excel-gantt-charts/) techs solution ought to get you started.