Excel line diagram x-axis labels by week

Question

I have a data series with daily values which I would like to display as a line diagram, like:

| date       | value |
|------------|-------|
| 12/12/2018 | 1202  |
| 13/12/2018 | 283   |
| 14/12/2018 | 226   |
| 15/12/2018 | 12    |
| 16/12/2018 | 55    |
| 17/12/2018 | 153   |
| 18/12/2018 | 123   |

The x-axis should, however, not contain the actual dates, but instead be labeled by week number as shown here:

with the week numbers in the middle of each week. Is there any way to do this? Here is the full sample dataset.

Answer 1

This will work best if you have data for every day of the week, if you don't, some labels may be missing and others will not be evenly distributed along the axis.

1) Insert a column to the left of your dates.

2) In this column, insert the following formula:

=IF(WEEKDAY(B2,2)=3,WEEKNUM(B2),"")  

and populate down. You should have something that looks like this:

3) Select the three columns and insert a line graph.

4) Remove the date column from the plotted data:

You should then have a chart that looks like this:

If you need the week numbers to be distributed evenly on the axis, you'll need to generate a new tables will all the dates (use the same formula to the left of the dates and a VLOOKUP for your data -- the cells filled with #N/A won't appear on the chart)

If you need different week numbers, you can adjust the formula to taste.