# Thread: List dates between start and end date

1. hi all,

I get always Start and Enddays of employees when they go on holidays.
I can calculate, how many workdays lay between those dates (formula networkdays), but what I need is a list of
all single workdays from start to end to get out a absencecalendar for the employee ( high days and holidays + absencetime) like

Start: 06.03.2010
End 14.03.2010

List: (Output)
08.03.2010
09.03.2010
10.03.2010
11.03.2010
12.03.2010

stef

2. Suppose your dates are in cells A1 and A2.

Select as many cells as you expect to get dates below each other and enter this array formula:

=SMALL(IF(((WEEKDAY(A1+ROW(INDIRECT("1:" & A2-A1)))=7)+(WEEKDAY(A1+ROW(INDIRECT("1:" & A2-A1)))=1))>0,"",A1+ROW(INDIRECT("1:" & A2-A1))),ROW(INDIRECT("1:" & A2-A1)))

Confirm using control+shift+enter, which will make the formula look like this:

{=SMALL(IF(((WEEKDAY(A1+ROW(INDIRECT("1:" & A2-A1)))=7)+(WEEKDAY(A1+ROW(INDIRECT("1:" & A2-A1)))=1))>0,"",A1+ROW(INDIRECT("1:" & A2-A1))),ROW(INDIRECT("1:" & A2-A1)))}

3. hi Jan,

I tried Your formula, but it won´t work correct (maybe something happened from english to german transfer). As You can see i figured out a vba solution which lists all dates between two given dates; the only thing I have to find out is how to eliminate weekend days...
stef[attachment=88301:Timelist.xls]

4. See attached...

5. hi,

but is there a possibility for getting workdays in my Code as well?

Stef

6. Two things.

Code:
```Sub WriteDates()
Dim sc As Range

sd = Range("G3")    ' start date
ed = Range("G4")    ' end date
Set sc = Range("C2")    ' start cell

' check dates

If ed - sd <= 0 Then Exit Sub
j = 0
For i = sd To ed
If Application.Weekday(i) <> 1 And Application.Weekday(i) <> 7 Then
sc.Offset(j, 0) = i
j = j + 1
End If
Next i```
2. Your weekday cells in column E are nonsense, weekday returns a number between 1 and 7 (1=Sunday, 7 is Saturday, depending on the optional second argument.
To see which days are in column C, just format the cells to include the day string...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•