DATEDIF Months
Results 1 to 2 of 2

Thread: DATEDIF Months
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2018
    Location
    Japan
    Posts
    227
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default DATEDIF Months

    I have the formula for the duration of months between 2 dates. However, I need to add 1 month if the end month days is greater than the beginning month. See below for example;

    I tried using EOMONTH, but that doesn't seem to do what I need it to. The following Code goes in the Months Required.
    Code:
    =IF([@ECC]=[@EAS],DATEDIF([@EAS],EOMONTH(EASCO,0),"M"),DATEDIF([@ECC],EOMONTH(EASCO,0),"M"))
    EASCO= 19-Mar-19
    ECC EAS Months Required
    2-Feb-19 2-Feb-19 1 (needs to be 2 since the day in Mar (19) is greater than the day in Feb (2).
    21-Jan-19 5-Feb-19 2 (Correct)
    Helper columns are the way of the future!

    All recommendations/work is completed in Excel 2013.
    ------
    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  2. #2
    Board Regular
    Join Date
    Mar 2018
    Location
    Japan
    Posts
    227
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DATEDIF Months

    I was able to work this out. Changed it to days, and divided by 30. Also added a statement that if the ECC or EAS is greater than or equal to EASCO, then the result is 0.

    Please note that this solution will not work for everyone as there are more than or in some cases less than 30 days in a month. But for our purposes, 30 days works exactly as needed.

    Code:
    =ROUNDUP(IF(OR([@ECC]>=EASCO,[@EAS]>=EASCO),0,IF([@ECC]=[@EAS],DATEDIF([@EAS],EOMONTH(EASCO,0),"d"),DATEDIF([@ECC],EOMONTH(EASCO,0),"d")))/30,0)
    Helper columns are the way of the future!

    All recommendations/work is completed in Excel 2013.
    ------
    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •