+ Reply to Thread
Results 1 to 19 of 19

final date formula not work

  1. #1
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    final date formula not work

    Hello to all.
    Because if the date of the start date is greater than the date today the month of final date increases by 1?
    Example:

    data inzio (start) 01/12/78
    data attuale ora (TODAY) 13/09/17
    data finale (end) 01/02/22 ed è esatto / correct

    data inzio 11/12/78
    data attuale ora 13/09/17
    data finale11/12/22 ed è esatto.

    data inzio 15/12/78
    data attuale 13/09/17
    data finale 15/01/23 non è esatto. dovrebbe essere 15/12/22. / not exact correct is 15/12/22

    I hope I explained.
    max
    Attached Files Attached Files

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

    Re: final date formula not work

    Please post an xlsx file, since you use Excel 2007. Please post an xlsx file, since you use Excel 2007.

    https://www.excelforum.com/excel-cha...ml#post4723023
    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 Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: final date formula not work

    here it is
    Attached Files Attached Files

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

    Re: final date formula not work

    can you also decribe your problem in english.

    maybe you can post for 3 examples the expected result.

  5. #5
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: final date formula not work

    data inzio (start) 01/12/78
    data attuale ora (TODAY) 13/09/17
    data finale (end) 01/02/22 ed è esatto / correct

    data inzio 11/12/78
    data attuale ora 13/09/17
    data finale11/12/22 ed è esatto.

    data inzio 15/12/78
    data attuale 13/09/17
    data finale 15/01/23 non è esatto. dovrebbe essere 15/12/22. / not exact correct is 15/12/22

    ---------------------------

    date start 01/12/78
    date today 13/09/17
    date end 01/02/22 correct

    date start 11/12/78
    date today 13/09/17
    date end 11/12/22 correct

    date start 15/12/78
    date today 13/09/17
    date end 15/01/23 not correct correct is 15/12/22

  6. #6
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: final date formula not work

    data inzio (start) 01/12/78
    data attuale ora (TODAY) 13/09/17
    data finale (end) 01/02/22 ed è esatto / correct

    data inzio 11/12/78
    data attuale ora 13/09/17
    data finale11/12/22 ed è esatto.

    data inzio 15/12/78
    data attuale 13/09/17
    data finale 15/01/23 non è esatto. dovrebbe essere 15/12/22. / not exact correct is 15/12/22

    ---------------------------

    date start 01/12/78
    date today 13/09/17
    date end 01/02/22 correct

    date start 11/12/78
    date today 13/09/17
    date end 11/12/22 correct

    date start 15/12/78
    date today 13/09/17
    date end 15/01/23 not correct correct is 15/12/22

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

    Re: final date formula not work

    date start 15/12/78
    date today 13/09/17
    date end 15/01/23 not correct correct is 15/12/22
    Why should the end date by 15/01/23 => please explain => exact 45 years?

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: final date formula not work

    I think your addition is not working right.

    In I12 and 13, you calculate "years and months" between start and today. If start is 15 Dec 78 and today is 13 sep 17, it calculates a difference of 38 years 8 months (464 months). However, 15 Dec 78 + 464 months is 15 August 17, almost a whole month short of today.

    Then you take the total time (44 years or 528 months), subtract the previous calculation (which, as noted, is almost a month off) to get the remaining time (528-464=64 months) and add that to today. In short, it looks like a rounding error caused by rounding the first difference down to the nearest month.

    Solution -- probably depends on exactly what you are trying to do. My approach would probably be more:
    1) Add 44 years to start date [=EDATE(B1,44*12)] to get end date.
    2) Formulas like in I12 and I13 to get the difference between now and start and now and end. Recognize that you will still have to consider rounding error as you decide how to round these to whole months.
    2a) I might completely redo some of these formulas. For example, I might do I12 as =YEAR(B2-B1)-1900 to get the number of years between those two dates. To get months in I13, =MONTH(B2-B1). Formulas like that might be easier to work with than the combination of INT() and YEAR() and DATEDIF() and... that you are currently using.

    I think the key to this is understanding exactly how you want to round the date difference to whole months, and programming the formulas to accurately deal with those rounded values.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: final date formula not work

    Date B1 + Date B8 (validate data) = what day?

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: final date formula not work

    Quote Originally Posted by max_max View Post
    Date B1 + Date B8 (validate data) = what day?
    I'm not sure I understand. =EDATE(B1,44*12) should give a date exactly 44 years after start date. Your current value in B8 is a text string. You are extracting year and month from this text string in I17 and K17, so you could also use =EDATE(B1,$I$17*12+$K$17). Is that what you are asking about, or is there something else I am not understanding?

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

    Re: final date formula not work

    See the green cell in E7.

    The yellow cells E8 and I 8 are helpcells.

    Please reply if the result is correct.

  12. #12
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: final date formula not work

    Oeldere the correct result is in F7

  13. #13
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: final date formula not work

    Il risultato in F7 è esatto.
    è possibile una modifica?
    Se nella cella C6 è presente
    2 anno/i - 10 mese/i
    togliere da F7 2 anno/i - 10 mese/i
    max

    --------------------------------------------------

    The result in F7 is correct.
    is it possible to modify it?
    If in cell C6 is present
    2 anno/i - 10 mese/i (2 year(s) - 10 month(s))
    remove/subtract from F7 2 anno/i - 10 mese/i (2 year(s) - 10 month(s))
    max

  14. #14
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: final date formula not work

    The file correct in this

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

    Re: final date formula not work

    F5 =edate(f7,-f6)

  16. #16
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: final date formula not work

    Hi oeldere is perfect!
    A small change for cell F5
    If cell C6 is empty the valid cell is F7
    if cell c6 is not empty the valid cell is F5
    max_max
    Attached Files Attached Files

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

    Re: final date formula not work

    f5 =IFERROR(EDATE(F7,-F6),"")

    Edit:

    The correct formula has to be:

    =IF(C6="",F7,EDATE(F7,-F6))
    Last edited by oeldere; 09-14-2017 at 02:33 PM. Reason: edit added

  18. #18
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: final date formula not work

    Thanks you oeldere, it's perfect!
    A greeting
    max_max

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

    Re: final date formula not work

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

    It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

+ 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. Data Analysis: Compare initial date with final date by looking up the latest revision
    By White_Glint in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-08-2015, 02:31 PM
  2. [SOLVED] Total work sheets on a final work sheet
    By Treecko in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-26-2013, 11:19 AM
  3. Getting subtraction element to work on the final row rather than only the first
    By jarnold231 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2012, 03:24 PM
  4. replacing lowest test grade with final if final is higher formula help
    By colbyclay in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-16-2012, 02:48 AM
  5. final date formula from inicial and duration
    By kghisla in forum Excel General
    Replies: 2
    Last Post: 02-26-2011, 12:54 PM
  6. Replies: 5
    Last Post: 06-15-2010, 09:52 AM
  7. [SOLVED] query for work help! showing final result
    By fdabb in forum Excel General
    Replies: 5
    Last Post: 01-26-2006, 06:45 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