+ Reply to Thread
Results 1 to 5 of 5

[SOLVED]How do I make the workday formula count the start date as the first working day?

  1. #1
    Registered User
    Join Date
    03-16-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2007
    Posts
    2

    [SOLVED]How do I make the workday formula count the start date as the first working day?

    Hello,

    I am using excel to track the deadline for applications. The applicant has 8 working days (the first being the date of application) to the deadline. The problem being my formula only starts counting from the next working day, for example:

    =WORKDAY(A301,8,Workday!A$4:A$14)

    A301 = 15/3/12 which the formula gives as 27/3/12 for the deadine when it should read 26/3/12.
    (A4:A14 are the holidays on a separate page)

    The formula works fine for applications received on a weekend as they are not received on a working day and so that day does not need to be counted? I basically need the formula to count 7 days from the start date if the application is received on a weekday and 8 if received on a weekend.

    Could anyone help please?

    Thanks in advance
    Last edited by john_mac83; 03-17-2012 at 01:22 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: How do I make the workday formula count the start date as the first working day?

    Maybe this:

    =WORKDAY(A301,7,Workday!A$4:A$14)+(WEEKDAY(A301,2)>5)

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How do I make the workday formula count the start date as the first working day?

    Hi

    Something like this?

    =if(or(WEEKDAY(A301)=7,WEEKDAY(A301)=1)),WORKDAY(A301,8,Workday!A$4:A$14),WORKDAY(A301,7,Workday!A$4:A$14)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    03-16-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: How do I make the workday formula count the start date as the first working day?

    Thanks very much zbor that works perfectly. I have only just figured out how to use the workday function so hadn't come across the weekday one. Would you mind explaining the formula for me so I can understand exactly how you came up with it. The help section on excel isn't very informative.

    Many thanks again

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: How do I make the workday formula count the start date as the first working day?

    Workday add number of working days to a date (optionaly excluding holidays).
    You've figure that out.

    Weekday determines what is the weekday of desired date (output: 1,2,3,4,5,6 and 7).
    In 2nd criteria WEEKDAY(A301,2) a 6 is Saturday and 7 is Sunday.

    So it say: IF date is Saturday or Sunday (WEEKDAY(A301,2)>5) (actually if it's 6 or 7) then add 1. Else add 0 because it will return you TRUE or FALSE that can be calculated as 1 or 0.

    Date:
    15.3.2012 (Thursday) + 7 + if Thursday (4) is greater than 5 (NOT, so it's FALSE): 15.3.2012 + 7 + 0 = 26.3.2012
    17.3.2012 (Saturday) + 7 + IF Saturday (6) is greater than 5 (YES, so it's TRUE): 17.3.2012 + 7 +1 = 28.3.2012

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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