Excel Unplugged

Create a Date Series of all weekdays in Excel (omits Saturdays and Sundays)

 

Let’s say we want to create a series of dates from 1/1/2013 to 12/31/2013 but with a catch. We want to leave out all dates that occur on weekends. So all dates that represent Saturdays and Sundays. We can do this in Excel quite easily by using the Fill/Series… command.

We start of by putting the first date of the series (1/1/2013) in a cell. Then we go to HOME/Fill/Series…

Date Series of all weekdays in Excel (omits Saturdays and Sundays)

In the Series dialog box we select Columns in the Series in, Date in Type but the most important settings are Weekday in Date unit and 12/31/2013 in Stop value.

Date Series of all weekdays in Excel (omits Saturdays and Sundays)

After pressing OK we get

Date Series of all weekdays in Excel (omits Saturdays and Sundays)

Notice how the 5th and 6th of January are missing. Well I think you know why :). Again we are one step closer to eternal happiness.

Comments 2

  1. MF says:

    Cool tip. Drag the auto fill handler by right click would show the option too… but of course, it is better to assign the stop value in your example 🙂

  2. Tim says:

    Thanks once again for a neat trick to use in Excel…put in my now growing library.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: