+ Reply to Thread
Results 1 to 14 of 14

Adding the correct number of days considering a starting date

Hybrid View

  1. #1
    Registered User
    Join Date
    02-12-2014
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Adding the correct number of days considering a starting date

    I went through a lot of forums and was not able to find the solution for my problem.
    Problem: I have a start date, let's say 23-08-2016. If I want to add 3 months, I could just use edate(A1,B1). However, the number of months depends on a success rate, let's say 25%. The problem is that edate(A1;B1*C1) will assume that the number of months is 0,75 (3 months * 25%) and will round to 0 months.

    An extra difficulty is that if my start date is February and I would like to add 0,75 months (or other value), then the formula should now that February has 28 (or 29) days, April 30 days, May 31 days and so on.

    A1: 23-08-2016 | B1: 3 | C1: 25%

    I tried datedif, offset, and others, and was not able to achieve any good results, because the end date varies according to the month of the start date.

    Any help would be much appreciated.

    Thank you

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Adding the correct number of days considering a starting date

    You get better result on your question if you add a small excel file, without confidential information.

    Please also add the expected results manualy in your file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,392

    Re: Adding the correct number of days considering a starting date

    Firstly, EDATE itself knows about month lengths and leap years: type 30/11/2015 into A1 and =EDATE(A1,3) into B1 and you will see this.

    Can you explain clearly what you would expect 75% of three months to be? What date are you expecting your calculation above to return?

    EDIT: I wonder if this is what you are looking for?

    =EDATE(A1,ROUNDUP(B1*C1,0))
    Last edited by AliGW; 08-23-2016 at 09:58 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Adding the correct number of days considering a starting date

    Given that there is no finite number of days we can assign to represent 1 month, then trying to say 75% of 1 month will naturally be inherent with inaccuracies.

    I think the closest we can get to a finite number representing 1 month is
    (365.25*4)/48 = 30.4375

    So if we multiply the number of months to add by 30.4375, then multiply that result by 75%
    That's as close to accurate as you're going to get. I think.

    So if
    A1 = Start Date
    B1 = Number of Months
    C1 = Percentage

    And assuming we would want to round the result of that calculation (to add an even number of days, instead of like 20.7432)
    Try

    =A1+ROUND(B1*30.4375*C1,0)

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,392

    Re: Adding the correct number of days considering a starting date

    How about this?

    =A1+(DAYS(EDATE(A1,B1),A1)*C1)

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

    Re: Adding the correct number of days considering a starting date

    Quote Originally Posted by AliGW View Post
    How about this?

    =A1+(DAYS(EDATE(A1,B1),A1)*C1)
    Note that the DAYS function requires Excel 2013 or later.

    The OP's profile says they're using Excel 2007.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,392

    Re: Adding the correct number of days considering a starting date

    Unintentional duplicate post - sorry!
    Last edited by AliGW; 08-23-2016 at 09:56 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,392

    Re: Adding the correct number of days considering a starting date

    Quote Originally Posted by Tony Valko View Post
    Note that the DAYS function requires Excel 2013 or later.

    The OP's profile says they're using Excel 2007.
    Ah, yes - well-spotted!

    =A1+(DATEDIF(A1,EDATE(A1,B1),"d")*C1)

    Excel 2016 (Windows) 32 bit
    F
    G
    H
    2
    30/11/2015
    3
    25%
    3
    22/12/2015
    Sheet: Sheet6

    Excel 2016 (Windows) 32 bit
    F
    G
    H
    2
    30/11/2015
    3
    25%
    3
    =F2+(DATEDIF(F2,EDATE(F2,G2),"d")*H2)
    Sheet: Sheet6
    Last edited by AliGW; 08-23-2016 at 10:03 AM.

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Adding the correct number of days considering a starting date

    @AliGW

    Why not just
    EDATE(F2,G2)-F2

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,392

    Re: Adding the correct number of days considering a starting date

    Quote Originally Posted by Jonmo1 View Post
    @AliGW

    Why not just
    EDATE(F2,G2)-F2
    Can you explain what you mean? As I can't get that to work in the calculation.
    Last edited by AliGW; 08-23-2016 at 10:17 AM.

  11. #11
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Adding the correct number of days considering a starting date

    EDATE(F2,G2)-F2
    Gives the same result as
    DATEDIF(F2,EDATE(F2,G2),"d")

    So your formula
    =F2+(DATEDIF(F2,EDATE(F2,G2),"d")*H2)
    Replace the DateDif with just the edate-F2
    =F2+(EDATE(F2,G2)-F2*H2)

    But there will be the order of operations problem, so that needs to be
    =F2+((EDATE(F2,G2)-F2)*H2)

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,392

    Re: Adding the correct number of days considering a starting date

    OK - thanks. I couldn't get that to work here. I'll try again later, as I am away from my laptop at present.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,392

    Re: Adding the correct number of days considering a starting date

    @Jonmo - yes, you're right - that does the same job.

  14. #14
    Registered User
    Join Date
    02-12-2014
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Adding the correct number of days considering a starting date

    AliGW and all,
    Thank you very much, your solution proved to be spectacular and simple.
    Much appreciated.
    Joćo

+ 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] Formula Help: Determine days total from arrival to current date then stop adding days
    By Vicious00013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2016, 12:45 PM
  2. Date + #of days to Correct Quarter and Year
    By courthamr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-01-2016, 06:53 AM
  3. [SOLVED] Find days between days, but including the starting date.
    By Jocamo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2015, 10:37 PM
  4. [SOLVED] Adding Total Number of Days Elapsed and Displaying >31 Days
    By cwwazy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-03-2013, 08:18 AM
  5. Replies: 2
    Last Post: 11-09-2012, 12:01 PM
  6. Date calculations - adding number of days to a date
    By Dave Goldman in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-12-2011, 12:44 PM
  7. [SOLVED] correct date/days in a template
    By Dumber than a pocket full of rocks in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 07-03-2006, 06:55 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