### Tags: assumes, excel, existing, five-day, function, microsoft, ms_excel, msdn, six-day, software, workday, working

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

On Microsoft » Microsoft Excel

20,098 words with 11 Comments; publish: Fri, 23 May 2008 17:35:00 GMT; (30693.75, « »)

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?

*http://excel.questionfor.info/q_microsoft-excel_235150.html*

All Comments

Leave a comment...

- 11 Comments
- 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?

#1; Fri, 23 May 2008 17:36:00 GMT

- A1: start date
- 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" 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?

#2; Fri, 23 May 2008 17:37:00 GMT

- Dear Teethless mama,
- =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.B>Mohan

> "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?

#3; Fri, 23 May 2008 17:38:00 GMT

- =WORKDAY(A1+1,A2-1)
- 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" <PBMohan.excel.questionfor.info.discussions.microsoft.com> wrote in message

news:334A5677-6990-4140-8C02-DCE7A106FAB4.excel.questionfor.info.microsoft.com...

> 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" 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?

#4; Fri, 23 May 2008 17:40:00 GMT

- Go into the VBA editor (Alt+F11), click Insert/Module on its menu bar and
- 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)" <rick.newsNO.SPAM.excel.questionfor.info.NO.SPAMverizon.net> wrote in

message news:O%23e61dKoIHA.4024.excel.questionfor.info.TK2MSFTNGP06.phx.gbl...

> 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" <PBMohan.excel.questionfor.info.discussions.microsoft.com> wrote in message

> news:334A5677-6990-4140-8C02-DCE7A106FAB4.excel.questionfor.info.microsoft.com...

>> 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" 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?

>

#5; Fri, 23 May 2008 17:41:00 GMT

- The code I posted is a **modification** of a 5-day workweek function I have
- 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)" <rick.newsNO.SPAM.excel.questionfor.info.NO.SPAMverizon.net> wrote in

message news:uIBqzjKoIHA.4580.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

> 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)" <rick.newsNO.SPAM.excel.questionfor.info.NO.SPAMverizon.net> wrote in

> message news:O%23e61dKoIHA.4024.excel.questionfor.info.TK2MSFTNGP06.phx.gbl...

>> 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" <PBMohan.excel.questionfor.info.discussions.microsoft.com> wrote in message

>> news:334A5677-6990-4140-8C02-DCE7A106FAB4.excel.questionfor.info.microsoft.com...

>> 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" 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?

>

#6; Fri, 23 May 2008 17:42:00 GMT

- Damn! I changed the function name and forgot to change the return name for
- 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?

#7; Fri, 23 May 2008 17:43:00 GMT

- A1: start date
- I don't think your formula skips over Sundays correctly. Try this...
A1: 3/29/2008

A2: 19 <<and>> 20

Rick

"N Harkawat" <NHarkawat.excel.questionfor.info.discussions.microsoft.com> wrote in message

news:36905743-B943-41F1-89A6-0E5F076CC859.excel.questionfor.info.microsoft.com...

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

#8; Fri, 23 May 2008 17:44:00 GMT

- I don't think your formula skips over Sundays correctly. Try this...
- The OP needs a *general* function for a *six* day workweek... your formula
won't work in the general case.

Rick

"Teethless mama" <Teethlessmama.excel.questionfor.info.discussions.microsoft.com> wrote in message

news:046A5244-A5CA-4905-9FAE-81AFE3F63E20.excel.questionfor.info.microsoft.com...

> =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.B>Mohan

>> "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?

#9; Fri, 23 May 2008 17:45:00 GMT

- The OP needs a *general* function for a *six* day workweek... your formula
- 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

#10; Fri, 23 May 2008 17:46:00 GMT

- Hello,
- 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)" <rick.newsNO.SPAM.excel.questionfor.info.NO.SPAMverizon.net> wrote in

> message news:uIBqzjKoIHA.4580.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

> > 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)" <rick.newsNO.SPAM.excel.questionfor.info.NO.SPAMverizon.net> wrote in

> > message news:O%23e61dKoIHA.4024.excel.questionfor.info.TK2MSFTNGP06.phx.gbl...

> >> 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" <PBMohan.excel.questionfor.info.discussions.microsoft.com> wrote in message

> >> news:334A5677-6990-4140-8C02-DCE7A106FAB4.excel.questionfor.info.microsoft.com...

> >> 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" 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?

> >>

> >

>

#11; Fri, 23 May 2008 17:47:00 GMT

- Thanks a lot to Teethless mama, Rick Rothstein, N.Harkawat and P.Bernd for