+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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,011

    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
    Excel 2016
    Posts
    12,321

    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.

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

    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
    Excel 2016
    Posts
    12,321

    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,011

    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,011

    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
    Excel 2016
    Posts
    12,321

    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
    8,470

    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,011

    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
    8,470

    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
    Excel 2016
    Posts
    12,321

    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,011

    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,011

    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,011

    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
    Excel 2016
    Posts
    12,321

    Re: final date formula not work


+ Reply to Thread
Page 1 of 2 1 2 LastLast

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