+ Reply to Thread
Results 1 to 3 of 3

Calculate weekday end date based on sum of weekday start date and cell value

  1. #1
    Forum Contributor
    Join Date
    07-22-2009
    Location
    Minneapolis, MN
    MS-Off Ver
    2016
    Posts
    220

    Calculate weekday end date based on sum of weekday start date and cell value

    It seems like this shouldn't be that hard, but for some reason I'm stumped.

    I have a cell that has a weekday start date. I have another cell that has a number in it for the duration of work days. I need a formula that will add the duration of work days to the start date, and return a weekday completion date. The part I'm hung up on is making sure that the completion date is a weekday.

    If the start date is a Monday, and the duration is one day, the completion date would be Monday. If the start date is a Monday, and the duration is 2 days, the completion date would be Tuesday. If the start date is a Thursday, and the duration is 3 days, the completion date would be the following Monday. I hope you can see the pattern.

    Here is the formula I have so far; it's close, but it doesn't seem to span weekends quite right and I'm not sure what/ how it needs to change.

    Cell A1 = Start Date (a weekday)
    Cell A2 = Number of days (duration)
    Cell A3 = End Date (a weekday)

    A3 = IF((A1=""),"",IF((A2=""),"TBD",(A2+A1)+CHOOSE(WEEKDAY((A2+A1)),1,1,1,1,1,3,2)))

    If there is a better solution please propose it!
    Thanks in advance!
    Last edited by Rerock; 08-01-2014 at 09:06 AM.

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

    Re: Calculate weekday end date based on sum of weekday start date and cell value

    Try using WORKDAY function, i.e.

    = IF(A1="","",IF(A2="","TBD",WORKDAY(A1-1,A2)))
    Audere est facere

  3. #3
    Forum Contributor
    Join Date
    07-22-2009
    Location
    Minneapolis, MN
    MS-Off Ver
    2016
    Posts
    220

    Re: Calculate weekday end date based on sum of weekday start date and cell value

    I didn't know there was a WORKDAY function!!

    That is a much more simple solution, and works perfectly; thanks!!!

+ 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. Calculate next due date based on start date frequency and current date
    By ironoverload in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-09-2014, 07:08 AM
  2. Help Returning The Week Day of a Date Without Using DATE and WEEKDAY Functions
    By vampirealexa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-15-2013, 02:12 AM
  3. [SOLVED] If date = weekday, ignore cell range
    By rachel.dudley in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2013, 06:41 AM
  4. Formula to calculate date from, weekday-weeknumber-year
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-16-2012, 06:59 PM
  5. [SOLVED] Calculate date following a specified weekday
    By batteredveg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-24-2011, 12:46 PM

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