Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    can you help me, please???
    stef

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)))}
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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]
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    See attached...
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi,
    thanks for Your Array solution;

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

    Stef

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Two things.

    Modify your routine like this:
    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...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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