Date add for working days

[stali]

I'm trying to automatically calculate 5 working days (no weekends) from Now(). Any suggestions? Thanks in advance

 

[DarkSun]

try this:

=IF(WEEKDAY(NOW())=1,NOW()+5,IF(WEEKDAY(NOW())=2,NOW()+4,NOW()+6))

make sure you test it by replacing NOW() with a cell reference and entering some dates.

Not sure If I got the formula right. It includes the current day. ie if it was monday it would return the date of friday.

 

[MichaelRed]

I did a generic function. Providing optional parameters for the starting date and the number of days of offset makes the function more versitile. For this exercise, I set the defaults for the Input date as "NOW()", and the default Number of days to offset the given date to 5, so the fiunction with NO arguments provides a "parameterless" function for stali.

I included minimal "nicieties", like error checking.

I did NOT include any provision for Holidays, which would be a common requirement for schedualing meetings and business events, but which would also entail the generation & maintance of a storage mechanisim for the specific holiday dates. Having 'been there', I am sure that this will become a "DO that", however this is " ... left as an exercise ... "

Public Function basAddWrkDays(Optional NDays As Integer = 5, Optional DateIn As Date) As Date

    Dim TestDays As Integer
    Dim Idx As Integer

    TestDays = NDays

    If (CLng(DateIn) = 0) Then
        DateIn = Now
    End If

    Do Until TestDays = 0

        If (Not (Weekday(DateIn + Idx) = 1) And Not (Weekday(DateIn + Idx) = 7)) Then
            TestDays = TestDays - 1
        End If

        Idx = Idx + 1

    Loop

    basAddWrkDays = DateAdd("d", Idx, DateIn)

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[CookieMonster]

Another proper and fast way of doing this is simply looping through each added day, while testing if we are dealing with a weekday, if so, add a day to compensate.
This function is preferred to using the weekday function,
because the weekday function is an Xl reference that not all Excel users have switched on. Note that you can replace vbSaturday and vbSunday by resp 6 and 7, depending on the date system used.

Example:

Function AddBussDays() As Long

For i = 1 To 5
If DateAdd("d", i + intWeekend, Date) = vbSaturday Or _
DateAdd("d", i + intWeekend, Date) = vbSunday Then
intWeekend = intWeekend + 1
End If
Next i

AddBussDays = DateAdd("d", i + intWeekend, Date)

End Function

This function includes the first day in the calculation, if you do not want this to happen, simply increase the loop to For i=1 to 6.

It's a simple!

Have Fun,
CookieMonster.