How do I use the WORKDAY function for a six-day working week?
 

The existing WORKDAY function in MS_Excel assumes a five-day working week
from Monday through Friday. How can I use the function in a six-day working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))1))


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working week
from Monday through Friday. How can I use the function in a six-day working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

Dear Teethless mama,

Thanks alot for your reply.

Suppose I have 29-Mar-08 in A1 (This is the start date of a job); I have 10
in A2 (This is the number of working days that the job takes); I would like
A3 to have the date on which the job would be complete.

29-Mar-08 is a Saturday. 10 working days means Monday to Saturday of the
next week (Sunday being a holiday) and Monday to Thursday of the week after
next ending on 10-Apr-08. How do I get this using a formula?

Thanks in advance, P.BMohan

"Teethless mama" wrote:

A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))1))


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working week
from Monday through Friday. How can I use the function in a six-day working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

=WORKDAY(A1+1,A2-1)


"P.B.Mohan" wrote:

Dear Teethless mama,

Thanks alot for your reply.

Suppose I have 29-Mar-08 in A1 (This is the start date of a job); I have 10
in A2 (This is the number of working days that the job takes); I would like
A3 to have the date on which the job would be complete.

29-Mar-08 is a Saturday. 10 working days means Monday to Saturday of the
next week (Sunday being a holiday) and Monday to Thursday of the week after
next ending on 10-Apr-08. How do I get this using a formula?

Thanks in advance, P.BMohan

"Teethless mama" wrote:

A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))1))


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working week
from Monday through Friday. How can I use the function in a six-day working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

Go into the VBA editor (Alt+F11), click Insert/Module on its menu bar and
Copy/Paste this code into the code window that appeared...

Function DateAddWorkDays(ByVal StartDate As Date, WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddWorkDays = DateAdd("d", 7 * (WorkDays \ 6) + (WorkDays Mod 6) - _
((WorkDays Mod 6) 7 - Weekday(StartDate)), StartDate)
End Function

If this is new to you, what you just did is create a user function which can
now be used just like any normal, built-in worksheet function within a
worksheet formula. To see this, using your example, go back to your
worksheet and put this formula in A3...

=DateAddWorkDays(A1,A2)

It should show, depending on how A3 is formatted, the date April 10, 2008.

Rick



"P.B.Mohan" wrote in message
...
Dear Teethless mama,

Thanks alot for your reply.

Suppose I have 29-Mar-08 in A1 (This is the start date of a job); I have
10
in A2 (This is the number of working days that the job takes); I would
like
A3 to have the date on which the job would be complete.

29-Mar-08 is a Saturday. 10 working days means Monday to Saturday of the
next week (Sunday being a holiday) and Monday to Thursday of the week
after
next ending on 10-Apr-08. How do I get this using a formula?

Thanks in advance, P.BMohan

"Teethless mama" wrote:

A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))1))


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working
week
from Monday through Friday. How can I use the function in a six-day
working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

The code I posted is a **modification** of a 5-day workweek function I have
posted in the past over in the compiled VB newsgroups in the past (compiled
VB does not have a function equivalent to Excel's WORKDAY function). Now,
the code I posted performs its calculations for a 6-day workweek (Sundays
off) and, as such, the name I used (from the original 5-day workweek
function) may be somewhat misleading. Here is the same code, but with a more
appropriate function name...

Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddWorkDays = DateAdd("d", 7 * (WorkDays \ 6) + (WorkDays Mod 6) - _
((WorkDays Mod 6) 7 - Weekday(StartDate)), StartDate)
End Function

Given this change, your worksheet formula in A3 would now be this...

=DateAddSixDayWorkweek(A1,A2)

Rick



"Rick Rothstein (MVP - VB)" wrote in
message ...
Go into the VBA editor (Alt+F11), click Insert/Module on its menu bar and
Copy/Paste this code into the code window that appeared...

Function DateAddWorkDays(ByVal StartDate As Date, WorkDays As Long) As
Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddWorkDays = DateAdd("d", 7 * (WorkDays \ 6) + (WorkDays Mod 6) - _
((WorkDays Mod 6) 7 - Weekday(StartDate)), StartDate)
End Function

If this is new to you, what you just did is create a user function which
can now be used just like any normal, built-in worksheet function within a
worksheet formula. To see this, using your example, go back to your
worksheet and put this formula in A3...

=DateAddWorkDays(A1,A2)

It should show, depending on how A3 is formatted, the date April 10, 2008.

Rick



"P.B.Mohan" wrote in message
...
Dear Teethless mama,

Thanks alot for your reply.

Suppose I have 29-Mar-08 in A1 (This is the start date of a job); I have
10
in A2 (This is the number of working days that the job takes); I would
like
A3 to have the date on which the job would be complete.

29-Mar-08 is a Saturday. 10 working days means Monday to Saturday of the
next week (Sunday being a holiday) and Monday to Thursday of the week
after
next ending on 10-Apr-08. How do I get this using a formula?

Thanks in advance, P.BMohan

"Teethless mama" wrote:

A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))1))


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working
week
from Monday through Friday. How can I use the function in a six-day
working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

Damn! I changed the function name and forgot to change the return name for
it within the body of the function itself. Here is the correct code for the
renamed function...

Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) _
7 - Weekday(StartDate)), StartDate)
End Function

Sorry for any confusion this may have caused.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
The code I posted is a **modification** of a 5-day workweek function I
have posted in the past over in the compiled VB newsgroups in the past
(compiled VB does not have a function equivalent to Excel's WORKDAY
function). Now, the code I posted performs its calculations for a 6-day
workweek (Sundays off) and, as such, the name I used (from the original
5-day workweek function) may be somewhat misleading. Here is the same
code, but with a more appropriate function name...

Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddWorkDays = DateAdd("d", 7 * (WorkDays \ 6) + (WorkDays Mod 6) - _
((WorkDays Mod 6) 7 - Weekday(StartDate)), StartDate)
End Function

Given this change, your worksheet formula in A3 would now be this...

=DateAddSixDayWorkweek(A1,A2)

Rick



"Rick Rothstein (MVP - VB)" wrote in
message ...
Go into the VBA editor (Alt+F11), click Insert/Module on its menu bar and
Copy/Paste this code into the code window that appeared...

Function DateAddWorkDays(ByVal StartDate As Date, WorkDays As Long) As
Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddWorkDays = DateAdd("d", 7 * (WorkDays \ 6) + (WorkDays Mod 6) - _
((WorkDays Mod 6) 7 - Weekday(StartDate)),
StartDate)
End Function

If this is new to you, what you just did is create a user function which
can now be used just like any normal, built-in worksheet function within
a worksheet formula. To see this, using your example, go back to your
worksheet and put this formula in A3...

=DateAddWorkDays(A1,A2)

It should show, depending on how A3 is formatted, the date April 10,
2008.

Rick



"P.B.Mohan" wrote in message
...
Dear Teethless mama,

Thanks alot for your reply.

Suppose I have 29-Mar-08 in A1 (This is the start date of a job); I have
10
in A2 (This is the number of working days that the job takes); I would
like
A3 to have the date on which the job would be complete.

29-Mar-08 is a Saturday. 10 working days means Monday to Saturday of the
next week (Sunday being a holiday) and Monday to Thursday of the week
after
next ending on 10-Apr-08. How do I get this using a formula?

Thanks in advance, P.BMohan

"Teethless mama" wrote:

A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))1))


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working
week
from Monday through Friday. How can I use the function in a six-day
working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

A1: start date
A2: number of days

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&(A1+A2))))=1))+A2+A1


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working week
from Monday through Friday. How can I use the function in a six-day working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

I don't think your formula skips over Sundays correctly. Try this...

A1: 3/29/2008
A2: 19 <<and 20

Rick


"N Harkawat" wrote in message
...

A1: start date
A2: number of days

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&(A1+A2))))=1))+A2+A1


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working week
from Monday through Friday. How can I use the function in a six-day
working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

The OP needs a *general* function for a *six* day workweek... your formula
won't work in the general case.

Rick


"Teethless mama" wrote in message
...
=WORKDAY(A1+1,A2-1)


"P.B.Mohan" wrote:

Dear Teethless mama,

Thanks alot for your reply.

Suppose I have 29-Mar-08 in A1 (This is the start date of a job); I have
10
in A2 (This is the number of working days that the job takes); I would
like
A3 to have the date on which the job would be complete.

29-Mar-08 is a Saturday. 10 working days means Monday to Saturday of the
next week (Sunday being a holiday) and Monday to Thursday of the week
after
next ending on 10-Apr-08. How do I get this using a formula?

Thanks in advance, P.BMohan

"Teethless mama" wrote:

A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))1))


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working
week
from Monday through Friday. How can I use the function in a six-day
working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

Hello,

Just another approach:

Take the difference of the two dates minus the number of sundays
between these two
(first formula on http://www.sulprobil.com/html/date_formulas.html)

No volatile worksheet function, Analysis Toolpak or VBA necessary...

Regards,
Bernd

How do I use the WORKDAY function for a six-day working week?
 

Thanks a lot to Teethless mama, Rick Rothstein, N.Harkawat and P.Bernd for
your help. I settled for Rick's macro.

Thanks, Mohan

"Rick Rothstein (MVP - VB)" wrote:

Damn! I changed the function name and forgot to change the return name for
it within the body of the function itself. Here is the correct code for the
renamed function...

Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) _
7 - Weekday(StartDate)), StartDate)
End Function

Sorry for any confusion this may have caused.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
The code I posted is a **modification** of a 5-day workweek function I
have posted in the past over in the compiled VB newsgroups in the past
(compiled VB does not have a function equivalent to Excel's WORKDAY
function). Now, the code I posted performs its calculations for a 6-day
workweek (Sundays off) and, as such, the name I used (from the original
5-day workweek function) may be somewhat misleading. Here is the same
code, but with a more appropriate function name...

Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddWorkDays = DateAdd("d", 7 * (WorkDays \ 6) + (WorkDays Mod 6) - _
((WorkDays Mod 6) 7 - Weekday(StartDate)), StartDate)
End Function

Given this change, your worksheet formula in A3 would now be this...

=DateAddSixDayWorkweek(A1,A2)

Rick



"Rick Rothstein (MVP - VB)" wrote in
message ...
Go into the VBA editor (Alt+F11), click Insert/Module on its menu bar and
Copy/Paste this code into the code window that appeared...

Function DateAddWorkDays(ByVal StartDate As Date, WorkDays As Long) As
Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddWorkDays = DateAdd("d", 7 * (WorkDays \ 6) + (WorkDays Mod 6) - _
((WorkDays Mod 6) 7 - Weekday(StartDate)),
StartDate)
End Function

If this is new to you, what you just did is create a user function which
can now be used just like any normal, built-in worksheet function within
a worksheet formula. To see this, using your example, go back to your
worksheet and put this formula in A3...

=DateAddWorkDays(A1,A2)

It should show, depending on how A3 is formatted, the date April 10,
2008.

Rick



"P.B.Mohan" wrote in message
...
Dear Teethless mama,

Thanks alot for your reply.

Suppose I have 29-Mar-08 in A1 (This is the start date of a job); I have
10
in A2 (This is the number of working days that the job takes); I would
like
A3 to have the date on which the job would be complete.

29-Mar-08 is a Saturday. 10 working days means Monday to Saturday of the
next week (Sunday being a holiday) and Monday to Thursday of the week
after
next ending on 10-Apr-08. How do I get this using a formula?

Thanks in advance, P.BMohan

"Teethless mama" wrote:

A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))1))


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working
week
from Monday through Friday. How can I use the function in a six-day
working
week situation from Monday through Saturday?

How do I use the WORKDAY function for a six-day working week?
 

I would be grateful if you could help me with codes where holidays are
excluded in below codes.

With anticipated thanks,

Jean-Paul


"Rick Rothstein (MVP - VB)" wrote:

Damn! I changed the function name and forgot to change the return name for
it within the body of the function itself. Here is the correct code for the
renamed function...

Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) _
7 - Weekday(StartDate)), StartDate)
End Function

Sorry for any confusion this may have caused.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
The code I posted is a **modification** of a 5-day workweek function I
have posted in the past over in the compiled VB newsgroups in the past
(compiled VB does not have a function equivalent to Excel's WORKDAY
function). Now, the code I posted performs its calculations for a 6-day
workweek (Sundays off) and, as such, the name I used (from the original
5-day workweek function) may be somewhat misleading. Here is the same
code, but with a more appropriate function name...

Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddWorkDays = DateAdd("d", 7 * (WorkDays \ 6) + (WorkDays Mod 6) - _
((WorkDays Mod 6) 7 - Weekday(StartDate)), StartDate)
End Function

Given this change, your worksheet formula in A3 would now be this...

=DateAddSixDayWorkweek(A1,A2)

Rick



"Rick Rothstein (MVP - VB)" wrote in
message ...
Go into the VBA editor (Alt+F11), click Insert/Module on its menu bar and
Copy/Paste this code into the code window that appeared...

Function DateAddWorkDays(ByVal StartDate As Date, WorkDays As Long) As
Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddWorkDays = DateAdd("d", 7 * (WorkDays \ 6) + (WorkDays Mod 6) - _
((WorkDays Mod 6) 7 - Weekday(StartDate)),
StartDate)
End Function

If this is new to you, what you just did is create a user function which
can now be used just like any normal, built-in worksheet function within
a worksheet formula. To see this, using your example, go back to your
worksheet and put this formula in A3...

=DateAddWorkDays(A1,A2)

It should show, depending on how A3 is formatted, the date April 10,
2008.

Rick



"P.B.Mohan" wrote in message
...
Dear Teethless mama,

Thanks alot for your reply.

Suppose I have 29-Mar-08 in A1 (This is the start date of a job); I have
10
in A2 (This is the number of working days that the job takes); I would
like
A3 to have the date on which the job would be complete.

29-Mar-08 is a Saturday. 10 working days means Monday to Saturday of the
next week (Sunday being a holiday) and Monday to Thursday of the week
after
next ending on 10-Apr-08. How do I get this using a formula?

Thanks in advance, P.BMohan

"Teethless mama" wrote:

A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))1))


"P.B.Mohan" wrote:

The existing WORKDAY function in MS_Excel assumes a five-day working
week
from Monday through Friday. How can I use the function in a six-day
working
week situation from Monday through Saturday?