How to produce a list of weekdays

If you want to make a list showing dates only for Monday – Friday, there are several ways you can do this

Using a weekday formula

You can use the Weekday calculation to identify when a simple A1+1 type list will generate a Friday and then jump to the Monday:

A2 = If(weekday(a1,2)=5,A1+3,A1+1)

This will add 1 day to the date above unless the date above is a Friday, in which case it will add 3 to show the Monday. If you have a different weekend, you can choose other days to be 1 by varying the “,2” entry.

Using a workday formula

An alternative formula that can avoid holidays is WORKDAY. In its simplest form, it is

A2=workday(a1,1)

This will generate a list of dates increasing by 1, avoiding the weekends. To include holidays we have two options. The first is to have a simple list of all dates that are holidays, so a three day holiday would be represented as three consecutive dates. If we name this list of dates as “hols”, the formula becomes:

A2=workday(a1,1,hols)

However, we may want to display holidays as a start date and the number of days taken off. If so, we can list the start dates and the days off in a 2 column named range called holidays.

A2 = WORKDAY(A1,IFERROR(VLOOKUP(A1+1,Holidays,2,FALSE),1),hols)

This works by replicating the original “1” where there is no mention of the date in the holidays listing (i.e. the Vlookup resolves to an error because the date isn’t mentioned); however, it adds the appropriate number of days if the start date is shown.

Using Autofill

Excel has a useful method of filling cells with a sequence, for instance, Mod, Tue, Wed etc. This can be used for dates. If you fill in A1 with a date, for example, 22/11.21 and then drag the bottom left corner of the cell down as far as you want, it will add 1 day to each cell.

However, this shows the weekends which you don’t want. At the bottom left of the list, there is a small box which, if you click it, will give options, one of which is only to fill Weekdays

autofill options

If the Autofill is not adding a sequence of days and is only showing the same thing, there are a couple of things to check

· Are you using a filter in the sheet – this will sometimes prevent Autofill from working. If so, switch off the filter and try again.

· In the options (tools / options or File / options depending on your version of excel), ensure that the following items are checked:

autofill settings

Showing weekday names

If you want to show the names of the week, you can do this in two simple ways

Set the format of the cells to show or include the weekdays. To do this, click on the drop-down box a the bottom of the Number ribbon, or press <ALT><O><E>

displaying the cell formatting box

Chose the Custom option and enter a date format that defines the way that the date should be formatted, for instance

· DDD

· DDDD

· DDD-dd/mm/yy

· etc

If you want to show the days in a separate column, you can use the WEEKDAY formula again:

B1 = Choose(weekday(A1,2),”Mon”,”Tue”,”Wed”,”Thu”,”Fri”)