Dynamic Chart Depiction On Excel

Occasional Contributor

I've a chart in excel that shows a person's start and end date. Depending upon day difference, it displays the data as follows:

 

sample_output.png

It shows person id in the left and the bar depicts the total days. For the day difference, I used this formula:

=(C2 - B2) + 1 

Simple enough! But now I've a requirement where I've to show fixed days in the horizontal line, say days up to 60 and if a person has total days of 10 calculating its start and end dates, the chart should depict accordingly. As an example, a person has total 10 days from 07/01/2021 to 07/10/2021, then the chart should increment the bar chart one by one based on date. Say 07/01/2021 is day 1, 07/02/2021 day 2 and so on. The bar chart will raise based on the days counted each day. I am not sure if this can be done using chart or any other tool in excel? Would expect your valuable suggestion in this regard - Thanks.

 

 

 

7 Replies

@AT-2017 Perhaps you mean that the horizontal axis should always show 0 to 60 days. Then, in Excel for the desktop you can over-ride the maximum bound of the axis and set it to 60. To do that, right-click on the horizontal axis, choose Format Axis and change the setting. It will then look like this.

Screenshot 2021-07-11 at 07.46.38.png

Now, you also tagged your post "Excel on Mobile". I believe you can't do it there. So you would have to set up the chart on the desktop version first. If it is possible in the mobile version, I just haven't found it. And that's probably because I hardly ever use that platform.

Thanks for the idea and really appreciate it. But what about this - As an example, a person has total 10 days from 07/01/2021 to 07/10/2021, then the chart should increment the bar chart one by one based on date. Say 07/01/2021 is day 1, 07/02/2021 day 2 and so on. The bar chart will raise based on the days counted each day (From previous post, probably when someone opens an excel file each day)? Is there any easy way to make it work?

@AT-2017 When you say "each day", do yo mean you want a label on the horizontal axis for every day, i.e. 1,2,3,4 etc. all the way to 60? Or perhaps gridlines for every day and labels every 10th?

Screenshot 2021-07-11 at 10.28.26.png

 

Screenshot 2021-07-11 at 10.27.03.png

@Riny_van_Eekelen No, that's fine. Let it be just 10. You can see there's a column days that's the difference of start and end date. I just want to check the days in the horizontal line increases one by one on date parameter. Say start date is 07/01/2021, so the chart increases to one in the horizontal line, then for 07/01/2021, it'll again progress to 2. Finally when it'll come to date 7/10/2021, then we'll see the line depicts on the horizontal line 10 (No more to go). So here, date will be tracked each time checking the excel file, I believe.

@AT-2017 Is your problem solved now or do you still need something else? If not solved, I'm afraid I've lost you and just don't understand what you are asking for.

@Riny_van_Eekelen please see this sample and just check the id with 1002 as well Date in the right. When the date will change, the bar chart will increase one by one. Though no functionality is required to change the date, just on daily-basis (After 24 hours) the bar will be increased.

 

sample.gif

I think I might know what you are meaning, I think you want to have the formula to mark progression between dates. Like the progress of a time-limited contract.

Here is the formula: =IF(AND(C2>I2,(I2-B2)+1>=0),(I2-B2)+1,(C2-B2)+1)

Put this should show the progression between the dates assuming the Date is representing today's date. Any dates that start before the "today's" date will show as 0.