+ Reply to Thread
Results 1 to 7 of 7

Calculate End Date given only Start Date and Duration

  1. 09-06-2018, 03:25 PM #1
    xtinct2
    xtinct2 is offline
    Forum Contributor
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    MS Office: Excel 365
    Posts
    163

    Calculate End Date given only Start Date and Duration

    Hi --

    I have a file in which people input start date and duration (i.e. # of months, in .5 increments -- so it could be 4 or 5.5, for instance).

    From this basic data, I'm hoping to be able to calulate the end date, rounded to the half month (half month could be defined as the 15th, i dont mind).

    For instance, if something has a start date of 4/13/2018 and a duration of 2, then the end date should be something like 6/15/2018. Or, if the duration was 2.5, the end date should be 6/30/2018.

    Another example is if someone put a start date of 3/25/2018 and a duration of 2, I'd like for the end date to show 5/31/2018.

    *IF* it's easier to do w/o the rounding to the half month, that is fine as well.
    (Bonus points if you have a formula for each method)

    Is this possible?

    Thank you!
    Reply With QuoteRegister To Reply
  2. 09-06-2018, 03:42 PM #2
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,891

    Re: Calculate End Date given only Start Date and Duration

    Hmm, shouldn't 2 month from 4/13/2018 be 6/13/2018? Duration in days of 61.

    Since days in a month varies, most duration calculation should be done in weeks/days. There is no accurate way to calculate duration in month with fraction.

    You could try something like below to get approximation...
    Assuming A2 has start date and B2 has duration in months.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Will return 6/13/2018 for duration of 2, and 6/28/2018 for 2.5.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein
    Reply With QuoteRegister To Reply
  3. 09-06-2018, 08:41 PM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,406

    Re: Calculate End Date given only Start Date and Duration

    I took a guess that the half months include the last day of each month.

    In the attached find a named formula in Name Manager called EOY. It is a short way to refer to 12/31/2018. This formula needs it to return a sufficiently large array of dates.

    Also in the attached the Number of days and their corresponding result dates are color coded to match. (I wasn't sure how to do the layout.

    In G2 array enter, fill across column K and down.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Here's the layout.


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    Start Dates
    Num days
    Num days
    Num days
    Num days
    Num days
    2
    4/13/2018
    2
    2.5
    3
    4
    5.5
    6/15/2018
    6/30/2018
    7/15/2018
    8/15/2018
    9/30/2018
    3
    3/25/2018
    2
    2.5
    3
    4
    5.5
    5/31/2018
    6/15/2018
    6/30/2018
    7/31/2018
    9/15/2018
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  4. 09-07-2018, 12:49 AM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,406

    Re: Calculate End Date given only Start Date and Duration

    Another way that does not require array entering of formulas.

    The same layout as previously. This time with two helper columns that return "target" dates.

    In M1 filled down and across until you get blanks.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Then in G2 filled down and across.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.




    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    1
    Start Dates
    Num days
    Num days
    Num days
    Num days
    Num days
    4/15/2018
    3/31/2018
    2
    4/13/2018
    2
    2.5
    3
    4
    5.5
    6/15/2018
    6/30/2018
    7/15/2018
    8/15/2018
    9/30/2018
    4/30/2018
    4/15/2018
    3
    3/25/2018
    2
    2.5
    3
    4
    5.5
    5/31/2018
    6/15/2018
    6/30/2018
    7/31/2018
    9/15/2018
    5/15/2018
    4/30/2018
    4
    5/31/2018
    5/15/2018
    5
    6/15/2018
    5/31/2018
    6
    6/30/2018
    6/15/2018
    7
    7/15/2018
    6/30/2018
    8
    7/31/2018
    7/15/2018
    9
    8/15/2018
    7/31/2018
    10
    8/31/2018
    8/15/2018
    11
    9/15/2018
    8/31/2018
    12
    9/30/2018
    9/15/2018
    13
    10/15/2018
    9/30/2018
    14
    10/31/2018
    10/15/2018
    15
    11/15/2018
    10/31/2018
    16
    11/30/2018
    11/15/2018
    17
    12/15/2018
    11/30/2018
    18
    12/31/2018
    12/15/2018
    19
    12/31/2018
    20
    Reply With QuoteRegister To Reply
  5. 09-07-2018, 01:16 PM #5
    jtakw
    jtakw is offline
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Calculate End Date given only Start Date and Duration

    Hi,

    I also came up with formulas for both cases.

    Assumes and using 15 days for half ( .5 ) months.
    D2 formula shows Actual Date after duration.
    E2 formula Rounds Up to 15th or End of month.

    Did quite a bit of testing, seems to hold up and not fail.

    Either formula, regular entered:

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    1
    		 Start Date Duration
    2
    4/13/2018
    2
    6/13/2018
    6/15/2018
    3
    4/13/2018
    2.5
    6/28/2018
    6/30/2018
    4
    4/25/2018
    2
    6/25/2018
    6/30/2018
    5
    4/25/2018
    2.5
    7/10/2018
    7/15/2018
    6
    4/1/2018
    2
    6/1/2018
    6/15/2018
    7
    4/1/2018
    2.5
    6/16/2018
    6/30/2018
    8
    4/5/2018
    3.5
    7/20/2018
    7/31/2018
    9
    4/16/2018
    4.5
    8/31/2018
    8/31/2018
    10
    2/15/2018
    2
    4/15/2018
    4/15/2018
    11
    2/15/2018
    2.5
    4/30/2018
    4/30/2018
    12
    2/16/2018
    4.5
    7/1/2018
    7/15/2018
    13
    5/16/2018
    2.5
    7/31/2018
    7/31/2018
    14
    5/16/2018
    4.5
    10/1/2018
    10/15/2018
    Sheet: Sheet63

    Excel 2016 (Windows) 64 bit
    D
    2
    =EDATE(A2,B2)+MOD(B2,1)*30
    Sheet: Sheet63

    Excel 2016 (Windows) 64 bit
    E
    2
    =IF(AND(DAY(A2)<=15,MOD(B2,1)=0),EDATE(A2,B2)-DAY(A2)+15,IF(MOD(B2,1)=0,EOMONTH(A2,B2),IF(DAY(EDATE(A2,B2)+15)>15,EOMONTH(EDATE(A2,B2),0),EOMONTH(EDATE(A2,B2),0)+15)))
    Sheet: Sheet63
    Last edited by jtakw; 09-07-2018 at 02:35 PM.
    Reply With QuoteRegister To Reply
  6. 09-10-2018, 04:43 AM #6
    soledad
    soledad is offline
    Forum Contributor
    Join Date
    11-21-2013
    Location
    zimbabwe
    MS-Off Ver
    Excel 2003
    Posts
    124

    Re: Calculate End Date given only Start Date and Duration

    Try this
    A1=input date
    B1=No.months
    C1=
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 09-10-2018, 12:23 PM #7
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Calculate End Date given only Start Date and Duration

    In C3 then drag down
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate Finish Date by adding start date with duration and Exclude weekends
    By coolcruiser in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 06-12-2018, 11:42 AM
  2. excel chart with date, start and end times (duration)
    By ravivaranasi in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-09-2016, 01:18 PM
  3. Calculate completion date from start date and duration - only work days
    By Robsheep in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-23-2015, 03:31 AM
  4. 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
  5. Calculating a minutes duration based on start and end date and times
    By chrispx001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-12-2013, 08:16 AM
  6. Show overlapping FY's from a Start Date and Duration
    By leaning in forum Excel General
    Replies: 7
    Last Post: 12-16-2010, 12:48 PM
  7. Calculate START date based on duration (work hours) and END date
    By kaaver in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-06-2010, 12:21 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1