Workdays with Saturdays for Dummies

[Elena]

Good Morning--

I found a couple of formulae on page 22 of this forum, but
cannot determine exactly how to make them work.

The situation is thus:

Enter a date that a program is due to enter testing or
whatever. The dates for all steps due before the move date
are automatically calculated and entered into cells.

I have it working with the WORKDAYS function, but was just
told that Saturdays must be included.

I tried entering all the serial numbers of all Sundays in
2004 into my Holiday array, but I can't figure out how to
make the straight date calculation look it up, and if the
calculated date lands on one of those days, to add 1 to
the date that is then inserted into the cells.

Any ideas would be greatly appreciated. And if you could
add a note explaining where I need to indicate cells in
the formula, I would be forever in your debt!

Thanks in advance.

Elena

 

[Paul]

Good Morning--

I found a couple of formulae on page 22 of this forum, but
cannot determine exactly how to make them work.

The situation is thus:

Enter a date that a program is due to enter testing or
whatever. The dates for all steps due before the move date
are automatically calculated and entered into cells.

I have it working with the WORKDAYS function, but was just
told that Saturdays must be included.

I tried entering all the serial numbers of all Sundays in
2004 into my Holiday array, but I can't figure out how to
make the straight date calculation look it up, and if the
calculated date lands on one of those days, to add 1 to
the date that is then inserted into the cells.

Any ideas would be greatly appreciated. And if you could
add a note explaining where I need to indicate cells in
the formula, I would be forever in your debt!

Thanks in advance.

Elena

As your workdays are not Mon-Fri, forget WORKDAYS altogether. Write your
formulas just as you would if any day of the week was allowable. Then change
each of your formulas like this:
=YourFormula+(WEEKDAY(YourFormula)=1)

 

[Alan]

If I understand you correctly
=IF(WEEKDAY(A1)=1,A1+1,A1)
will change Sunday to Monday and leave all other days unchanged, you would
need to build this into your formula. Apologies if this isn't what you want.
Regards,

 

[Guest]

I'll give that a try. Thanks so much!

-----Original Message-----
If I understand you correctly
=IF(WEEKDAY(A1)=1,A1+1,A1)
will change Sunday to Monday and leave all other days unchanged, you would
need to build this into your formula. Apologies if this isn't what you want.
Regards,



.

 

[Guest]

Thanks so much! I'll give that a try.

Have a great day!

 

[Harlan Grove]

I have it working with the WORKDAYS function, but was just
told that Saturdays must be included.

....

Another alternative.

http://groups.google.com/[email protected]

 

[Ron Rosenfeld]

Good Morning--

I found a couple of formulae on page 22 of this forum, but
cannot determine exactly how to make them work.

The situation is thus:

Enter a date that a program is due to enter testing or
whatever. The dates for all steps due before the move date
are automatically calculated and entered into cells.

I have it working with the WORKDAYS function, but was just
told that Saturdays must be included.

I tried entering all the serial numbers of all Sundays in
2004 into my Holiday array, but I can't figure out how to
make the straight date calculation look it up, and if the
calculated date lands on one of those days, to add 1 to
the date that is then inserted into the cells.

Any ideas would be greatly appreciated. And if you could
add a note explaining where I need to indicate cells in
the formula, I would be forever in your debt!

Thanks in advance.

Elena

Here is a UDF that will work.

To enter it, <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer Window, then
Insert/Module.

Paste the code below into the window that opens.

You can then use =WD(StartDate, NumOfDays, Holidays) as you would the WORKDAY
formula. Except that it will give a #VALUE! error if NumOfDays = 0

-------------------------
Function WD(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing) As Date

Dim i As Long
Dim TempDate As Date
Dim c As Range
Dim Stp As Integer

Stp = Abs(NumDays) / NumDays
TempDate = StartDate
For i = Stp To NumDays Step Stp
TempDate = TempDate + Stp

If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)

If Not Holidays Is Nothing Then
Do Until Not IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False
If IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False Then
TempDate = TempDate + Stp

If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)
End If
Loop
End If
Next i

WD = TempDate
End Function

===================


--ron