+ Reply to Thread
Results 1 to 4 of 4

WORKDAY.INTL in Excel 2007 to exclude Fri/Sat

  1. #1
    Registered User
    Join Date
    02-27-2017
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    2

    Question WORKDAY.INTL in Excel 2007 to exclude Fri/Sat

    Hello everyone,

    My first post here, nice to meet you all.
    I'm trying to solve my problem and actually close to it, missing just the last step, that's why I'd like to ask you for help.

    I want to prepare a resolution time spreadsheet in Excel 2007 that shows when each team should finish their tasks within their working hours and excluding weekends.

    The main part of this (working hours) is working, I just can't find a way to change the excluded weekend days from Saturday/Sunday to Friday/Saturday for some of the teams.


    Here's my code as it is now:
    Start time is =Now() A2 = 2/27/17 4:59 PM
    Hours to add B2 in format hh:mm
    End Date in date format C2
    Please Login or Register  to view this content.
    End Time in format hh:mm D2
    Please Login or Register  to view this content.
    Begin time of 1st shift (before midnight) in format hh:mm F2
    End time of 1st shift (till midnight) in format hh:mm G2

    +optional if shift goes through midnight
    Begin time of 2nd shift (after midnight) in format hh:mm F3
    End time of 2nd shift (after midnight) in format hh:mm G3
    Total hours of both shifts in format hh:mm H3
    Please Login or Register  to view this content.
    Newer excel versions use WORKDAY.INTL function, where value 7 = Friday/Saturday as weekend, can you please help me somehow include it into my existing/working WORKDAY function?

    Thank you very much, really appreciate any help with this!
    Sam

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: WORKDAY.INTL in Excel 2007 to exclude Fri/Sat

    Welcome to the forum.

    Have a look at Chip Pearson's site - he has some 2007-compatible formulae which allow you to specify weekends: http://www.cpearson.com/excel/BetterNetWorkDays.aspx
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: WORKDAY.INTL in Excel 2007 to exclude Fri/Sat

    Hello Sam,

    As long as your "weekend" is still 2 consecutive days then it's relatively easy to "offset" WORKDAY and NETWORKDAYS functions to do what you want, e.g. instead of this WORKDAY formula to add 1 working day to a date (considering weekend to be Sat/Sun)

    =WORKDAY(B1,1)

    you can use this version for Fri/Sat weekend

    =WORKDAY(B1+1,1)-1

    [That gives the same results as =WORKDAY.INTL(B1,1,7)]

    so in your case that means that this formula

    =WORKDAY(A2,CEILING(ROUND((B2+MOD(A2,1)-F$2-IF(MOD(A2,1)>G$2,F$3-G$2))/H$3,9),1)-1)

    simply changes to this:

    =WORKDAY(A2+1,CEILING(ROUND((B2+MOD(A2,1)-F$2-IF(MOD(A2,1)>G$2,F$3-G$2))/H$3,9),1)-1)-1

    Note that you can get Thu/Fri weekend by using +2/-2 etc.

    For NETWORKDAYS it's similar

    =NETWORKDAYS(A1,B1) for Sat/Sun

    would become

    =NETWORKDAYS(A1+1,B1+1)

    which is the equivalent of

    =NETWORKDAYS.INTL(A1,B1,7)

    Note: if you want to use holiday ranges this method is still valid but you also need to OFFSET the holiday ranges, e.g.

    =NETWORKDAYS(A1+1,B1+1,INDEX(Holidays+1,0))
    Audere est facere

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: WORKDAY.INTL in Excel 2007 to exclude Fri/Sat

    Welcome back, DLL!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel Workday function to exclude non business hours
    By Sele in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2016, 05:20 AM
  2. NETWORKDAYS.INTL in 2007
    By nickh1981 in forum Excel General
    Replies: 4
    Last Post: 08-11-2015, 02:45 AM
  3. [SOLVED] Help with Workday.intl
    By NeedForExcel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-30-2015, 07:01 AM
  4. [SOLVED] WORKDAY.INTL function for excel 2007
    By turist in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2013, 02:20 AM
  5. Workday.intl problems
    By JacoKanban in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-06-2013, 06:40 PM
  6. Workday.intl function--Holidays help
    By bluskye425 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-24-2013, 02:28 PM
  7. Workday.INTL or Workday function issue
    By junoon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2013, 03:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1