Excel Date Axis Exclude Weekends? How-To

Yes, you can make an Excel chart axis exclude weekends, but the method depends on your Excel version. In Excel 2013 and later, the date axis has a built-in “Days between dates” option that lets you hide weekends. In older versions, you must use a scatter plot with a hidden weekday column as the axis. This article explains both approaches, the exact steps, and common pitfalls.

Formula

The calculator above works out your average pace (minutes per kilometer) and speed (kilometers per hour) for a run or ride. The formulas are:

• Pace = time (minutes) ÷ distance (km)
• Speed = distance (km) ÷ (time (minutes) ÷ 60)

Where:

• t = finish time in minutes (e.g., 50 minutes)
• d = distance in kilometers (e.g., 10 km)

Pace is shown in minutes per kilometer (min/km), and speed in kilometers per hour (km/h).

Worked Example

Using the default values: t = 50 minutes, d = 10 km.

1. Pace = 50 ÷ 10 = 5.0 min/km
2. Speed = 10 ÷ (50 ÷ 60) = 10 ÷ 0.8333 = 12.0 km/h

The calculator returns a pace of 5.00 min/km and a speed of 12.00 km/h. These are the numbers you see in the headline output.

Why Exclude Weekends from a Date Axis?

When you plot daily data (e.g., stock prices, sales, server uptime) on a line chart, Excel’s default date axis treats every calendar day equally. That creates a flat, horizontal gap between Friday and Monday because the axis includes Saturday and Sunday even though you have no data for those days. The gap makes trends look like they have a plateau or a dip that isn’t real. Excluding weekends removes those gaps so the line flows continuously from Friday to Monday, showing the actual sequence of trading or working days.

How to Exclude Weekends in Excel 2013 and Later

Modern Excel (2013, 2016, 2019, 365) has a built-in axis option:

1. Right-click the date axis (the horizontal axis) and choose Format Axis.
2. In the Axis Options pane, find Bounds and Units.
3. Look for Days between dates – it’s a checkbox or a drop-down depending on the version.
4. Check the box or select Days from the drop-down.
5. Excel automatically hides weekend dates on the axis and compresses the gaps.

If you don’t see that option, you likely have a text axis (because the data isn’t recognized as dates) or you’re using an older Excel. In that case, use the scatter plot method below.

How to Exclude Weekends in Older Excel (Scatter Plot Method)

For Excel 2010 and earlier, or when the built-in option doesn’t work, use a scatter plot with a hidden weekday number column:

1. Add a helper column next to your data. In each row, enter a formula that returns a sequential number only for weekdays. For example, if your dates are in column A, put =IF(WEEKDAY(A2,2)<6, COUNTIF($A$2:A2,"<"&A2)+1, "") – this counts how many weekdays have occurred up to that date.
2. Insert an XY Scatter chart (not a line chart) using the helper column as the X values and your data as the Y values.
3. Format the X-axis to hide the numbers or set the tick labels to show the actual dates (you can use a custom number format like dd-mmm).
4. The chart now plots points only for weekdays, with no weekend gaps.

This method works because the X-axis is numeric (the weekday count), not a date axis.

Common Problems and Mistakes

• Dates stored as text: If your date column is text, Excel treats it as a text axis. Convert to real dates by selecting the column and using Data > Text to Columns > Delimited > Next > Date (MDY).
• Hidden weekend data still appears: The built-in option only works if your data actually has rows for weekdays. If you have no data for a weekday, the axis will still show that day as a gap.
• Scatter plot doesn't show dates: By default, the X-axis shows numbers. Right-click the axis, choose Format Axis, and under Number apply a date format.
• Holidays are not weekends: The built-in option only removes Saturdays and Sundays. To exclude public holidays, you must use the scatter plot method and filter out those dates in the helper column.

Limitations and Alternatives

Excluding weekends changes the visual spacing, but it does not change the underlying data. The axis no longer represents true elapsed time – a Monday after a Friday appears immediately adjacent. This is fine for showing trends, but misleading if you need to compare durations (e.g., a project timeline). For true time gaps, keep the weekend dates on the axis. An alternative is to use a line chart with a text axis – this treats each category as equally spaced, but then you lose the date labels. Another approach is to use a dynamic array formula (in Excel 365) to filter out weekend rows before plotting, using FILTER and WEEKDAY.

Frequently Asked Questions

Why does my date axis still show weekends after I check the box?
Check that your data is recognized as dates. If the axis type is "Text" instead of "Date", the option won't appear. Also, the built-in option only hides weekends that have no data – if you have a data point on a Saturday, it will still show.

Can I exclude holidays too?
Yes, but only with the scatter plot method. Add a helper column that returns a sequential number only for weekdays that are not in your holiday list (e.g., using COUNTIF on the holiday range). Then plot against that helper.

Does this work for bar charts?
Yes, the built-in axis option works for bar and column charts as well. For older Excel, the scatter plot method only works for line and scatter charts – bar charts don't have an X-axis that can be numeric.

Will excluding weekends change my trendline?
No, the trendline is calculated from the data points, not the axis spacing. The slope and equation remain the same.

What if I only have data for weekdays but want to show the actual dates on the axis?
Use the scatter plot method and format the X-axis number format to display the date. You can also set the axis minimum and maximum to the first and last weekday numbers.

Is there a way to do this in Google Sheets?
Google Sheets doesn't have a built-in option, but you can use a similar scatter plot approach. Create a helper column with sequential numbers for weekdays, then insert a chart and select "Scatter chart". Format the horizontal axis to show the dates.

Related

• How to Back Up Chromebook Files Before Reset
• Phone Not Ringing? Find the Setting Behind Missed Calls
• How to Read Winter Tire Ratings for Snow, Ice & Wet Roads

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2