"Saturday as a work day?
 

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.

On Thu, 11 Aug 2005 12:00:20 -0700, "keith m" <keith
wrote:

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

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(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(I NDIRECT("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)


"keith m" <keith wrote in message
...
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.

On Thu, 11 Aug 2005 20:50:53 +0100, "Bob Phillips"
wrote:

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEK DAY(start_date+SIGN(days)*(
ROW(INDIRECT("1:"&ABS(days)*10))),2)<7)*ISNA(MATC H(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 days6553. 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

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)


"Ron Rosenfeld" wrote in message
...
On Thu, 11 Aug 2005 20:50:53 +0100, "Bob Phillips"
wrote:


=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEK DAY(start_date+SIGN(days)*
(

ROW(INDIRECT("1:"&ABS(days)*10))),2)<7)*ISNA(MATC H(start_date+SIGN(days)*(R
O

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 days6553. 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

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....

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)


"Giovanni D via OfficeKB.com" wrote in message
...
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....