+ Reply to Thread
Results 1 to 14 of 14

Excel 2007 : nesting if and datedif formula

  1. #1
    Registered User
    Join Date
    01-17-2011
    Location
    Oakville, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    9

    nesting if and datedif formula

    I am currently running a spreadsheet wanting to translate dates into months of service with 4 possible scenarios. $b$2 has been set up with the date of 1/1/10 as a reference point.

    The formula currently is set up as:

    =if((and(B19&C19="")),12,roundup(if(b19>$b$2,b19-$b$2,if(c19>$b$2,c19-$b$2,datedif(b19,c19,"D")))/30,0))

    The B column is Date of Hire and C column is Termination Date. I need the formula to give me months of service for the following scenarios:

    If both columns are blank, return 12
    If Date of Hire is not blank, return the difference between the start date and $b$2
    If Termination Date is not blank, return the difference between $b$2 and the termination date
    If both Date of Hire and Termination are not blank, return the difference between Termination Date and Date of Hire.

    Right now, this formula will return the correct info in the first 3 scenarios but not the last.

    Anyone able to provide some insight into this one??

    Thank you!
    Last edited by justagal; 01-17-2011 at 02:39 PM. Reason: Oops Unresolved

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: nesting if and datedif formula

    Try something like:

    =IF(AND(B19<>"",C19<>""),C19-B19,IF(B19<>"",$B$2-B19,IF(C19<>"",C19-$B$2,12)))

    Depending on what the reference date means, you may need to switch the $B$2-B19 to B19-$B$2 and the C19-$B$2 to $B$2-C19
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-17-2011
    Location
    Oakville, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: nesting if and datedif formula

    Unfortunately that did not solve the entire issue I am having. I have multiple if formulas because I need it to fulfill a variety of scenarios. Here is a snipit of the spreadsheet:

    B2 = 1/1/2010

    Column B Column C Column D
    Date of Hire Termination Date Service(months)
    12
    4/21/2010 4
    2/23/2010 2
    6/30/2010 8/20/2010 2 (current formula is returning 6 on this one)

    Current formula is:
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-17-2011
    Location
    Oakville, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: nesting if and datedif formula

    Sorry, I cannot get the columns to line up correctly to display here.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: nesting if and datedif formula

    try:

    =IF(AND(B19="",C19=""),12,ROUNDUP(IF(AND(B19<>"",C19<>""),DATEDIF(B19,C19,"d"),IF(B19>$B$2,B19-$B$2,C19-$B$2))/30,0))

  6. #6
    Registered User
    Join Date
    01-17-2011
    Location
    Oakville, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: nesting if and datedif formula

    Thank you that has helped tremendously but just one more thing. With a termination date of December 31st, it is returning 13 months. Any way for this to max out at 12 or ensure that it doesn't add an extra month?
    Thank you!

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: nesting if and datedif formula

    You can wrap a MIN around it..

    Please Login or Register  to view this content.
    Note I replaced DATEDIF with C19-B19 to be consistent with other differences...

  8. #8
    Registered User
    Join Date
    01-17-2011
    Location
    Oakville, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: nesting if and datedif formula

    Brilliant! That works perfectly. Thank you so much for your help!

  9. #9
    Registered User
    Join Date
    01-17-2011
    Location
    Oakville, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: nesting if and datedif formula

    OK New issues. In putting the min(12.... formula, it then changes prior dates with the months of service awarded. Specifically of concern is that I have a start date of 6/30/10 and a termination date of 8/20/10. With inputting this formula it returns a value of 1 and it should be 2. Also, I have a start date of 2/1/10 (no termination date) and it is returning a value of 12). Strange?!

    Again, $b$2 = 1/1/10
    b19 can equal 2/1/10 for the one scenario with c19 blank
    b19 can equal 6/30/10 and c19 can equal 8/20/10 for the second scenario

    the =min(12,.... formula fixes the issue with a termination date of 12/31/10 but creates the above noted issues.

  10. #10
    Registered User
    Join Date
    01-17-2011
    Location
    Oakville, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: nesting if and datedif formula

    Just the 2/1/10 date is still an issue. Sorry

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: nesting if and datedif formula

    For both of those scenarios, I get 2.... and has nothing to do with the MIN()

  12. #12
    Registered User
    Join Date
    01-17-2011
    Location
    Oakville, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: nesting if and datedif formula

    It appears that if the start date is the 1st of any month this changes the outcome with respect to months of service?

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: nesting if and datedif formula

    I think it is because you are dividing by 30... if you divide by 31, then I get 1...

    =MIN(12,IF(AND(B19="",C19=""),12,ROUNDUP(IF(AND(B19<>"",C19<>""),C19-B19,IF(B19>$B$2,B19-$B$2,C19-$B$2))/31,0)))

    Then it works, but might cause issues elsewhere.

    If you use DATEDIF

    =MIN(12,IF(AND(B19="",C19=""),12,IF(AND(B19<>"",C19<>""),DATEDIF(B19,C19,"m"),IF(B19>$B$2,DATEDIF($B$2,B19,"m"),DATEDIF($B$2,C19,"m")))))

    then this counts number of whole months between dates accounting for number of days... but it might not give the result you are wanting...

  14. #14
    Registered User
    Join Date
    01-17-2011
    Location
    Oakville, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: nesting if and datedif formula

    Ok, I think and truly believe this is finally working!! Thank you so much!!
    Last edited by justagal; 01-17-2011 at 03:25 PM. Reason: Solved

+ Reply to Thread

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