+ Reply to Thread
Results 1 to 8 of 8

ISBLANK is messing up my datedif calculation :( PLEASE HELP!

  1. #1
    Registered User
    Join Date
    05-02-2012
    Location
    indiana
    MS-Off Ver
    excel 2010
    Posts
    4

    ISBLANK is messing up my datedif calculation :( PLEASE HELP!

    I want to calculate the difference between two dates and display the answer in years, months, and days in the target cell, which I have done fine, BUT... I also want the target cell to remain blank if the second date hasn't been entered yet. Here's the set up...

    Cell C2: Entered into inventory date, D2 is the date it was taken out of inventory and J2 is my target cell.
    My "DATEDIF" formula that works is: =DATEDIF(C2,D2,"y") & " years, " & DATEDIF(C2,D2,"ym") & " months, " & DATEDIF(C2,D2,"md") & " days"&IF(ISBLANK(D2),"",)

    When I add the "ISBLANK" function to it, like this: =IF(ISBLANK(D2),"",(D2-C2))&DATEDIF(C2,D2,"y") & " years, " & DATEDIF(C2,D2,"ym") & " months, " & DATEDIF(C2,D2,"md") & " days"&IF(ISBLANK(D2),"",)

    It leaves the it blank like it's suppose to but the years calculate like it's not reading the / between the month and year in the dates so I end up with something like 480 years 1 month 2days.

    Please help, this is driving me crazy, I've worked on it for hours! Thank you!

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: ISBLANK is messing up my datedif calculation :( PLEASE HELP!

    try iferror

    =iferror(your formula here ,"")
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    05-02-2012
    Location
    indiana
    MS-Off Ver
    excel 2010
    Posts
    4

    Re: ISBLANK is messing up my datedif calculation :( PLEASE HELP!

    Nope, didn't work. It still gives me 140 years 0 months 14 days for the dates 5/1/2012 and 5/15/2012. Thanks for the help anyway and if you have anymore please keep them coming!

  4. #4
    Registered User
    Join Date
    05-02-2012
    Location
    indiana
    MS-Off Ver
    excel 2010
    Posts
    4

    Re: ISBLANK is messing up my datedif calculation :( PLEASE HELP!

    No wait that totally worked! I had a type o! Thank you thank you thank you!!! You rock~!

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: ISBLANK is messing up my datedif calculation :( PLEASE HELP!

    welcome. stick to the forum ok.

  6. #6
    Registered User
    Join Date
    05-02-2012
    Location
    indiana
    MS-Off Ver
    excel 2010
    Posts
    4

    Re: ISBLANK is messing up my datedif calculation :( PLEASE HELP!

    Ok, one more thing... Now that you've corrected that for me, I took out all the "starter" dates and find the my target cell auto fills with 0 years 0 months 0 days. How do I make it blank if there's no start date either?

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: ISBLANK is messing up my datedif calculation :( PLEASE HELP!

    =if(c2="","",iferror....

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: ISBLANK is messing up my datedif calculation :( PLEASE HELP!

    =IF(C2="","",IFERROR(DATEDIF(C2,D2,"y")&" years, "&DATEDIF(C2,D2,"ym")&" months, "&DATEDIF(C2,D2,"md")&" days",""))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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