How to list all dates between two dates in Excel

Every day from start to end with SEQUENCE

The example is a holiday cover rota: the office needs someone on call every day from Monday 21 December 2026 to Monday 4 January 2027. B1 holds the start, B2 the end.

SEQUENCE(rows, columns, start) counts up by 1 from the start value. Dates are whole numbers in Excel (one per day), so counting up from the start date gives the next day each row. The number of rows is B2-B1+1: the +1 is what makes the end date appear. Without it you get 14 dates and the list stops on 3 January.

The result spills down from the formula cell, 15 cells in all, and updates when you change either date. Leave the cells below empty, or the formula shows #SPILL!. Spill formulas also do not work inside an Excel Table, so put the list next to the table, not in it.

Make the list show as dates

SEQUENCE returns plain numbers, so a General cell shows 46377 instead of 21/12/2026. Select the spill area (or the whole column) and choose Home > Number Format > Short Date. For a rota, a custom format reads better: press Ctrl+1, choose Custom and type ddd d mmm to show Mon 21 Dec.

You can also wrap the formula in TEXT to get labels directly. That is handy for printed headings, but the result is text: it will not sort by date, cannot be used in date sums, and a lookup against real dates will not find it. Keep the real dates and change the format unless you only need labels.

For a calendar or timesheet header that runs across the page, swap the first two arguments: =SEQUENCE(1,B2-B1+1,B1) spills the same 15 dates across one row.

Weekdays only, and skipping holidays

To drop Saturdays and Sundays, filter the sequence with WEEKDAY. WEEKDAY(date,2) numbers Monday as 1 and Sunday as 7, so less than 6 keeps Monday to Friday. The rota has 11 weekdays between the two dates; the other 4 days are weekend days.

The office is also closed on 25 December, 28 December and 1 January (D2:D4). The cleanest way to skip those as well is WORKDAY. NETWORKDAYS counts the working days (8 here), SEQUENCE makes the numbers 1 to 8, and WORKDAY steps that many working days forward from the day before the start. Starting from B1-1 matters: from B1 itself the list would begin on 22 December.

If Saturday is a working day, use the .INTL versions with weekend code 11 (Sunday only): =WORKDAY.INTL(B1-1,SEQUENCE(NETWORKDAYS.INTL(B1,B2,11,D2:D4)),11,D2:D4) lists 10 dates. The FILTER route can skip holidays too, by adding *(COUNTIF(D2:D4,SEQUENCE(B2-B1+1,1,B1))=0) to the condition, but WORKDAY is shorter and easier to check.

Every Monday (or any weekday) between two dates

A weekly meeting runs from Wednesday 4 November 2026 (G1) to 31 March 2027 (G2), every Monday. Use SEQUENCE with a step of 7, starting from the first Monday on or after G1.

G1+MOD(2-WEEKDAY(G1),7) finds that Monday: WEEKDAY gives Sunday 1, Monday 2, and so on, and MOD works out how many days to move forward (0 if G1 is already a Monday). Here it lands on Monday 9 November. For another weekday, change the 2: 6 for Friday, 1 for Sunday.

The count is the gap from that Monday to the end, divided by 7, rounded down, plus 1. If that feels like too much arithmetic, the filter version gives the same 21 dates: =FILTER(SEQUENCE(G2-G1+1,1,G1),WEEKDAY(SEQUENCE(G2-G1+1,1,G1))=2).

Every month: same day, first day or last day

Months are not a fixed number of days, so do not use a step of 30. From 31 January, SEQUENCE(4,1,G3,30) gives 2 March, 1 April and 1 May, drifting away from month ends. Use EDATE, which moves by whole months, with SEQUENCE supplying 0, 1, 2 and so on.

EDATE handles short months: from a lease that starts on 31 January 2027 it gives 31 January, 28 February, 31 March and 30 April. Always count from the original start date, as this formula does. If you instead fill =EDATE(previous cell,1) down a column, February's 28 sticks and every later month lands on the 28th (28 March, not 31 March).

For month ends, use =EOMONTH(G1,SEQUENCE(DATEDIF(G1,G2,"m")+1,1,0)), which gives 30 November to 31 March for the meeting range. For the first of each month, =EOMONTH(G1,SEQUENCE(DATEDIF(G1,G2,"m")+1,1,-1))+1 gives 1 November to 1 March. Note that 1 November is before the 4 November start, so check whether that first month should be included. DATEDIF(G1,G2,"m") counts whole months between the dates (4 here), and +1 includes the starting month.

Older Excel: Fill Series and the fill handle

Without SEQUENCE (Excel 2019 and earlier), let Excel fill the dates. The result is fixed values, so if the start or end changes, you fill again.

The fill handle works too: type the start date, then drag the small square at the bottom right of the cell down with the right mouse button and choose Fill Days, Fill Weekdays, Fill Months or Fill Years when you let go. Fill Weekdays skips Saturdays and Sundays, but it knows nothing about holidays, so remove closure days by hand.

Watch for start dates that carry a time. If B1 is 21 December at 9:00, every date in the SEQUENCE list is also at 9:00, which breaks exact matches against plain dates in lookups and COUNTIF. Use INT(B1) and INT(B2) in the formula to drop the times. And if the end date is before the start, B2-B1+1 is zero or negative and SEQUENCE returns an error, so check the dates are the right way round.

1. Type the start date in the first cell, for example A5, and select it.
2. Go to Home > Fill > Series.
3. Choose Series in Columns and Type Date, then pick the Date unit: Day, Weekday, Month or Year.
4. Leave Step value at 1 (or 7 for weekly), type the end date in Stop value, for example 04/01/2027 in your system's date order, and click OK.