Need a Timeline on my X axis for a Gantt chart

Hi,

I've created a Gantt chart using a pivot chart from my data and I need the 'X' axis to show from 12am, 1am, 2am etc ... up to 11pm.

I saw some settings for the X axis that can be modified but I can't get them to work.

Can anyone help with this?

Thanks

Here are the files, Thanks

Yes, I'm sure we can help with this. I am not understanding what your pivot table and Gantt chart are supposed to show. The following explanation will make more sense if you understand how Excel stores dates and times as serial numbers (http://www.cpearson.com/Excel/datetime.htm ). What I see them showing (using staff1 as an example):
1) The pivot table sums up all of the start times (all 8 AM underlying serial number 0.33333) and there are 7 entries for staff1. So the pivot table is reporting 7*1/3 or 7/3 or 2.3333 -- meaning that the total start time is 2 1/3 days or 2 days and 8 hours or 56 hours (depending on number format). I don't know what "sum of start time" is really supposed to mean, but this does not seem like a meaningful value.
2) The duration column is a mix of numbers (E2-D2) and text outputs. The pivot table ignore the text outputs and sums up the numeric duration values (2*4 hours + 3 hours or 2*0.0.16667+0.125=0.458 or 11 hours). I don't know why you switched to a text output part way through the source data. It seems like these should all output numeric values rather than text strings in order for the pivot table to sum them up correctly.
As with the previous, I am not sure what it really means to sum up the durations, except to get a total duration, but I am not sure how that should be visualized on your Gantt chart.
3) Your Gantt chart then takes the total start time (2 1/3) and total duration (currently just less than 1/2 day) and makes a stacked bar chart. The "invisible" bar extends from 0 to 2 1/3 (or 56 hours) and the "duration" bar extends for 0.458 day (11 hours). If I format the horizontal axis to show elapsed hours ("[h]"), I see the duration bar start at 56 hours and extend to 67 hours. If I format to show "time of day" ("h A/P"), I see the duration bar extending from 8 AM of day 3 to 7 PM of day 3. This is what the raw numbers in your spreadsheet mean, but I don't know if this is what you are intending. For help with these number format codes, review: https://support.office.com/en-us/art...7-9c9354dd99f5

At this point, the Gantt chart is correctly showing what you have told it to show, but I don't know if it represents what you intended. The mindless answer to "how do I get an axis to display time of day (12 hour clock) from midnight to midnight?" is to use a time of day number format similar to "h A/P". In your case, that will cause the axis to display 3 days worth of midnight to midnight times. I don't think that result alone is what you are trying to do with your Gantt chart, but it's all I can suggest without a better understanding of what you are really trying to do.

Thanks Mrshorty,

I've made some progress which should display what I'm after.

The below link is about the settings to get a chart to display 12am throug to 11pm on the x axis but I haven't quite got it right yet.

https://stackoverflow.com/questions/...-plot-in-excel

I've attached the latest file to show where I'm at.

The file has the options to select from a drop down for the 'Type' and the 'Day' & I want see the gantt chart display that days hours worked.

Thanks

I'm not sure what more you are trying to do.

In order to get the horizontal axis to extend from midnight to 11 PM, I selected the horizontal axis and formatted the axis so it would have a fixed maximum at 0.96 (or 1.0 or similar value close to 1). That extended the horizontal axis all the way to 11 PM.

That's the only ting I see that is missing based on what I have understood. Is there more that I am missing?

HI MrShorty,

I've repeated what you've said but I get all times that aren't on the hour?

Did you actually do this on your computer?

To explain again, I want the X axis to display from 12am, 1am, ,,,, up to 11pm, I can't be much clearer than that.

I've made further progress using the attached settings for the axis & got the attached chart.

This is very close but I'm not sure why the times after 6pm have the extra minute? eg: 7:01pm, 8:01pm etc...

1 hour is not exactly 0.0417 of day. 1/24 is more of a non-terminating decimal 0.04166666666..... I think you will get rid of that extra minute on later times of day if you use a value with higher precision. Maybe 0.0416667 or 0.04166666667 (up to the limits of double precision's 15 digits).

Thanks I'll give that a try.