"Saturday as a work day?

K

keith m

I am using the formula "=Workday" but I want this formula to include
Saturdays. I have a Start Date and a duration and want to calculate the end
date with Saturday included as a work day. Can anyone tell me how to do
this? Any assistance would be greatly appreciated. Thank you.
 
R

Ron Rosenfeld

On Thu, 11 Aug 2005 12:00:20 -0700, "keith m" <keith
I am using the formula "=Workday" but I want this formula to include
Saturdays. I have a Start Date and a duration and want to calculate the end
date with Saturday included as a work day. Can anyone tell me how to do
this? Any assistance would be greatly appreciated. Thank you.

Here is a UDF (User Defined Function) that will accomplish that.

To enter this function, <alt><F11> opens the Visual Basic Editor. Ensure your
project is highlighted in the Project Explorer window, then Insert/Module and
paste the code below into the window that opens.

To use the function, in some cell enter the formula:

=WDincSat(Start,NumDays,Holidays)

The variables can be cell references or entered directly. The Holidays
argument can be a range; but it is optional.

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

'Workday function without Analysis Toolpak

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

Stp = Sgn(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

WDincSat = TempDate
End Function
========================================



HTH,
--ron
 
B

Bob Phillips

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(
ROW(INDIRECT("1:"&ABS(days)*10))),2)<7)*ISNA(MATCH(start_date+SIGN(days)*(RO
W(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW(INDIRECT("1:"&ABS(days)*10)
)),ABS(days)))

start_date, days, and holidays are named ranged here.

It is an arary formula so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

Ron Rosenfeld

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(
ROW(INDIRECT("1:"&ABS(days)*10))),2)<7)*ISNA(MATCH(start_date+SIGN(days)*(RO
W(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW(INDIRECT("1:"&ABS(days)*10)
)),ABS(days)))

start_date, days, and holidays are named ranged here.

It is an arary formula so commit with Ctrl-Shift-Enter

Your formula is handy, but will give a REF error if days>6553. I believe that
is because the ROW(INDIRECT("1: ...) gives a result of greater than 65536 for
the second factor, which would result in an invalid cell reference.


--ron
 
B

Bob Phillips

That is very true Ron, and must be for the reason you state. I will add that
to the text in future.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
G

Giovanni D via OfficeKB.com

Hi Sir this is Gio from Philippines...... it's my first time here in
this site... i am wondering how can i solve this problem in excel and please
help me.. I used excel 2000 in creating an inventory program in the hospital.
I used this excel inventorry program in our suppply room, i used one
worksheet per item. and i have almost 300 items in the supply room or almost
300 worksheets. I saved it as a template for all i know it is safer to save
it as template rather than saving it as ordinary excel files. The program was
working well, but not when i started linking(hyperlink) it from a certain
file that i always used. Then i have save it several times as a template but
i notice that the program malfuncitons, it doesnt compute the formulas i
created and some formulas are gone. Why is this happening. when i add some
items in the inventory it wouldnt add to the current balance, why is this
happening? Will you please help me, you wer the only people who can only
help me with this kind of problem......please....
 
B

Bob Phillips

Gio,

Re-post this as a new thread, it will get lost tagged onto this thread.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top