One of the most frequent tasks for any manager is “planning”, be it putting together a hiring schedule or designing a jumbo jet, it all starts with a simple project plan and gantt chart is simple and intuitive representation of the same. But how to make a gantt chart in excel without writing too many formulas or adding conditional formats? Do not worry! with the simple trick we are going to learn today, you will be able to “gantt in 60 seconds” 🙂
You dont have even 60 seconds, worry not, download the free gantt chart template and get your project plan ready in 6 seconds.
For our example purposes, we will look at a fictitious project plan shown below:
Even though you can use this trick to pretty much any data format, it works better when the project plan is structured around how I represented it above.
Now lets build a gantt chart in 60 seconds, get your stopwatches out and get, set …. GO!
1. Create a bar graph
Select the data part of your plan (ie all the cells except header row in the above table) and click on chart icon in excel. Select “bar chart” as chart type and “stacked bar 2d” as sub-type (2nd left on the top row) as shown here.
Click finish. At this point your gantt chart should look like this:
2. Transform your bar graph to gantt chart
Now we will convert this stacked bar graph to a gantt chart by using chart formatting options.
- First we will reverse the way data is charted, so that it looks like a gantt chart. For this we need to right click anywhere on the “y-axis” of the graph and select “format” option. Go to “scale” tab and check “Categories in reverse order” option.
Click ok, now out gantt chart should look like this:
btw, what is the time on that stop clock, 34 seconds, well, thats just fine, we have got plenty of time to spruce this up.
- Now, lets get rid of first data series so that our graph looks more like gantt chart. Select the first data series of your chart (should be in violet blue color 🙂 ), right click and go to “format data series” option.
Select “none” for “border” and “area” options in the “patterns” tab. This will make sure that the first series is invisible, so we see second data series floating on the chart, thus making it look almost like a gantt chart.
Go to “data labels” tab and check “category name” option. This will make sure our gantt chart will show labels (but on the now invisible first data series)
Click ok, at this point our gantt chart should look like this:
- Finally we will adjust labels and backgrounds to convert this to a perfect gantt chart
- First lets remove the legend box on the right by selecting it and hitting “del” key.
- Now, lets also remove the y-axis since labels are visible on data-series 1. Just click on the y-axis (or category axis) and hit “del” again.
- Lets adjust the alignment of the data labels on series 1 so that they are properly visible. Right-click on the data labels and select “format” option. Go to “alignment” tab in the dialog and select “Right” for horizontal alignment and “inside end” for label position. This will ensure that our data labels are right aligned and shown at the end of data series 1, ie closer to the actual series 2 (duration of the task). See the below screen cap for more help.
- Lets also remove the plot back ground to remove the annoying grey color from our gantt chart. Just click on the grey color anywhere and hit “del”. While we are at it, you can also change the line color of x-axis (the days) to white or transparent to reduce the eye sore.
- Finally, lets adjust the plot area size so that we can read all the data labels and everything looks normal.
At this point our gantt chart should look something like this:
If you still have few seconds left, you can tweak the chart format to make it look better. I had 3 more seconds left, so I tried this 🙂
Feel free to download the free gantt chart template and see how to create gantt charts using excel bar graphs.
Bonus tips for enthusiastic excel experimenters:
1. Adjust the grid line format to make them more subtle
2. Select a particular task’s data point and change its color to emphasize progress / stalled statuses
3. Enhance this to add another column with no. of resources (or difficulty etc.), add this to the stacked chart and make it invisible just like series 1, but show the data labels.
4. You get the picture… so start gantting… 😀
Also read:
Learn how to create project plans / gantt charts using conditional formatting
Create art grade excel charts with these 73 designer templates
Other uber cool excel tricks to make your colleagues zealous and your boss happy
27 Responses to “Sum of Values Between 2 Dates [Excel Formulas]”
I would apply a filter and use function subtotal, with option 9. This way you can see multiple views based on the filter.
hey Chandoo, the solutions you proposed are very efficient, but if I wanted to be fancy I would do it this way .. the references are as your example workbook.
=SUM(INDIRECT("C"&(MATCH(F5,B5:B95)+4)):INDIRECT("C"&(MATCH(F6,B5:B95)+4)))
I like things simple:
=SUMIF(B5:B95,">="&F5,C5:C95)-SUMIF(B5:B95,">"&F6,C5:C95)
use something like: =SUM(OFFSET(B1,0,0,DATEDIF(A1,D1,"d")))
and have D1 be the date that I want to sum to.
In Excel 2003 (and earlier) I'd use an array formula to calculate either with nested if statements (as shown here) or with AND.
{=SUM(IF(B5:B95>F5,IF(B5:B95<F6,C5:C95,0),0))}
Note that I truly made this for BETWEEN the dates, not including the dates
I turned the data set into a table named Dailies.
I named the two limits StartDate and EndDate.
And used an array formula:
{=SUM((Dailies[Date]>=StartDate)*(Dailies[Date]<=EndDate)*Dailies[Sales])}
If I would still be using the old Excel I would do it as follows:
SUMIF($B$5:$B$95,"<="&H6,$C$5:$C$95)-SUMIF($B$5:$B$95,"<"&H5,$C$5:$C$95)
Works as simple as it is.
Regards
=sum(index(c:c,match(startdate,c:c,1)+1):index(c:c,match(enddate,c:c,1))
=sum(index(c:c,match(startdate,b:b,1)+1):index(c:c,match(enddate,b:b,1))
Great examples and thanks to Chandoo. You have simplified my work.
Hi! great tips I have found in your page, have you seen this
http://runakay.blogspot.com/2011/10/searching-in-multiple-excel-tabs.html
[...] I'm not sure I understand your question fully, but have a look at this: Sum of Values Between 2 Dates [Excel Formulas] | Chandoo.org - Learn Microsoft Excel Online [...]
Thank you! Thank you! Thank you!
=SUMIF(A2:A11;">="&B13;B2:B11)-SUMIF(A2:A11;"<"&A11;B2:B11)
awesome... thank yoo Chandoo!
which is most efficient and fast, if all are efficient ?
Thank you for this formula, I've just spent ages trying to find something to work on my data, I knew it would be possible! Don't care if others think there are easier/other ways to do it, you explained it so I understood it and could apply it to what I was doing so I'm happy!
The above said example is awesome for calculating values between dates,
can you pls let know how to calculate sale values if we have 10 sales boys for
ex: 1,rama
2,krishna
3,ashwin
4,naga
5,suresh
how much rama sale value between 1/jan/2015 to 10/jun/15
how much krishna sale value between 10/jan/2015 to 15/july/2015
i think you understood can you pls let me know the formula for how to calculate the sale between diffrent sale man sale value from master data file
Thanks,
Nagaraju
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,C2:C100,">="&E2,C2:C100,"<"&F2)
Col. A Col. B Col. C Col.D Col. E Col. F
Name Date Sales
ABC 28-Jun-11 1 MNO 28-Jun-11 25-Sep-11
XYZ 29-Jun-11 0.5
MNO 30-Jun-11 1
PQR 1-Jul-11 1
Typo ERROR / Correction in formula:
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,B2:B100,">="&E2,B2:B100,"<"&F2)
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Viv
@Viv
Can you please post the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a file so that a specific answer can be delivered.
Thanks for this - it solved the problem that I was having. However can someone please explain to me why the "" needs to be around >= and <= as well as why we need to add & in order for the formula to work? Thanks in advance!
This formula works perfectly as well. Any ideas?: =SUM(INDEX(C5:C95,MATCH(H5,B5:B95,1)):INDEX(C5:C95,MATCH(H6,B5:B95,1)))
ikkeman had posted the same thing.
I am trying to sum total a range of cells between date ranges ie column n has $ amounts column d has the transaction dates ie 1/3/2015 or 25/3/2015 or 25/4/2015 column b has the text saying drp or distribution - reinv
In another cell I am trying to sum or total (in column n) with the value of a range of different dates (column d) that contain different text (column b) ie cell n48 is 50, n65 is 85, n165 is 36
with the dates ie cell d48 is 1/3/2015, d65 is 25/3/2015 and d165 is 25/4/2015
with different text that says drp or distribution - reinv ie cell b48 is drp, b65 is distribution - reinv, b165 is drp
If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp then the total would be 50. Also if I wanted to sum the amounts between 1/4/2015 to 30/4/2015 with drp the sum total would be 36 If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp and distribution - reinv the sum would be 115
What would the formula be for these different questions
hope you can help, it has been driving me nuts and cant work it out