+ Reply to Thread
Results 1 to 2 of 2

Problem with DATEDIF "m" not reognizing, for example that 6 months and 1 day > than 6month

  1. #1
    Registered User
    Join Date
    08-03-2015
    Location
    Bogota
    MS-Off Ver
    2010
    Posts
    1

    Problem with DATEDIF "m" not reognizing, for example that 6 months and 1 day > than 6month

    Hi,

    So I´m using the following formula:

    =IF(C2="Pre-sessional only",EDATE(F2,1),IF(O(C2="English",C2>=1,C2="Foundation"),IF(DATEDIF(E2,F2,"m")<=6,(F2+7),IF(DATEDIF(E2,F2,"m")<12,EDATE(F2,2),IF(DATEDIF(E2,F2,"m")>=12,EDATE(F2,4))))))

    I realise the above formula could be simplified but my question regards difference between months which remains regardless.....

    In the formula above when the difference between two dates I´m using is less than 6 months, between 6-12 months or more than 12 months, I want to add 7 days, 2 months or 4 months respectively, to a given date.

    The problem is that the formula only recognizes >6 months if the difference between the two dates is 7 completed months. It doesn´t recognize a difference of 6 days and 1 day for example as more than 6 months. Equally it doesn´t recognize that 12 months and a day is greater than 12 months. How do I solve this? I would use days instead but not every month has 30 days etc.

    Is there a formula that recognizes that 6 months a say 10 days is more than 6 months (even though it´s less than 7 months, or that 12 months and 2 days is more than 12 months (even though it´s less than 13 months?

    I´m sure there is a simple answer but I´m an Excel novice!!!

    Thank you

  2. #2
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Problem with DATEDIF "m" not reognizing, for example that 6 months and 1 day > than 6m

    Use the EDATE function, which you have done above.

    Example:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. using DATEDIF to find "life cycle" for devices
    By xcmm405 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2014, 09:23 AM
  2. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. Having trouble with formatting months and days as "MM" and "dd"
    By RyLH87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2013, 05:52 AM
  4. Why "datedif" function results sometimes negative numbers?
    By Ron Rosenfeld in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  5. Why "datedif" function results sometimes negative numbers?
    By Ambrosiy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  6. Does "DATEDIF" function count leap years?
    By malik641 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2005, 10:17 AM
  7. Why "datedif" function results sometimes negative numbers?
    By Ambrosiy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2005, 07:05 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1