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; (30678.13, « »)

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?

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
    • 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
    • =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
    • 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.todaysummary.com.discussions.microsoft.com> wrote in message

      news:334A5677-6990-4140-8C02-DCE7A106FAB4.excel.todaysummary.com.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
    • 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.todaysummary.com.NO.SPAMverizon.net> wrote in

      message news:O%23e61dKoIHA.4024.excel.todaysummary.com.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.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:334A5677-6990-4140-8C02-DCE7A106FAB4.excel.todaysummary.com.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
    • 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.todaysummary.com.NO.SPAMverizon.net> wrote in

      message news:uIBqzjKoIHA.4580.excel.todaysummary.com.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.todaysummary.com.NO.SPAMverizon.net> wrote in

      > message news:O%23e61dKoIHA.4024.excel.todaysummary.com.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.todaysummary.com.discussions.microsoft.com> wrote in message

      >> news:334A5677-6990-4140-8C02-DCE7A106FAB4.excel.todaysummary.com.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
    • 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
    • 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.todaysummary.com.discussions.microsoft.com> wrote in message

      news:36905743-B943-41F1-89A6-0E5F076CC859.excel.todaysummary.com.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
    • 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.todaysummary.com.discussions.microsoft.com> wrote in message

      news:046A5244-A5CA-4905-9FAE-81AFE3F63E20.excel.todaysummary.com.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
    • 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
    • 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.todaysummary.com.NO.SPAMverizon.net> wrote in

      > message news:uIBqzjKoIHA.4580.excel.todaysummary.com.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.todaysummary.com.NO.SPAMverizon.net> wrote in

      > > message news:O%23e61dKoIHA.4024.excel.todaysummary.com.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.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> news:334A5677-6990-4140-8C02-DCE7A106FAB4.excel.todaysummary.com.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