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

[P.B.Mohan]

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?

 

[Teethless mama]

A1: start date
A2: end date

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

 

[P.B.Mohan]

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

 

[Teethless mama]

=WORKDAY(A1+1,A2-1)

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

 

[Rick Rothstein \(MVP - VB\)]

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\)]

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\)]

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

 

[N Harkawat]

A1: start date
A2: number of days

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Bernd P]

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

 

[P.B.Mohan]

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

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

With anticipated thanks,

Jean-Paul