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

Asked By P.B.Moha on 17-Apr-08 03:45 AM
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?




Teethlessmam replied on 17-Apr-08 09:24 AM
A1: start date
A2: end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))>1))
PBMoha replied on 17-Apr-08 10:32 AM
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.B>Mohan
Teethlessmam replied on 17-Apr-08 12:06 PM
=WORKDAY(A1+1,A2-1)
Rick Rothstein \(MVP - VB\) replied on 17-Apr-08 12:24 PM
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
Rick Rothstein \(MVP - VB\) replied on 17-Apr-08 12:34 PM
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\) replied on 17-Apr-08 12:40 PM
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
NHarkawa replied on 17-Apr-08 12:43 PM
A1: start date
A2: number of days

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&(A1+A2))))=1))+A2+A1
Rick Rothstein \(MVP - VB\) replied on 17-Apr-08 12:54 PM
I do not think your formula skips over Sundays correctly. Try this...

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

Rick
Rick Rothstein \(MVP - VB\) replied on 17-Apr-08 12:57 PM
The OP needs a *general* function for a *six* day workweek... your formula
will not work in the general case.

Rick
Bernd P replied on 18-Apr-08 02:57 AM
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
PBMoha replied on 18-Apr-08 03:33 AM
Thanks a lot to Teethless mama, Rick Rothstein, N.Harkawat and P.Bernd for
your help. I settled for Rick's macro.

Thanks, Mohan
JP. replied on 27-Jun-08 09:09 AM
I would be grateful if you could help me with codes where holidays are
excluded in below codes.

With anticipated thanks,

Jean-Paul
CGable replied on 04-Jun-09 10:32 AM
You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.