Avatar of cfhasan1
cfhasan1
Flag for United States of America asked on

Date calculation between two dates

I’m try to calculate the # of days between dates and my formula is: IFERROR(DATEDIF(A2,B2, “d”),0) . This works partially, but when end is blank I get zero, which correct. I want modify formula to use today if end date is blank to return a value .
Microsoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Rob Henson

Try:

=IFERROR(DATEDIF(A2,IF(B2="",TODAY(),B2),"d"),0)
Rob Henson

Or if there's a chance that end date could be zero rather than blank:

=IFERROR(DATEDIF(A2,IF(OR(B2="",B2=0),TODAY(),B2),"d"),0)
cfhasan1

ASKER
Cells remain blank with this formula. Also remains blank when start is greater than end date
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rob Henson

Can you upload a file with some sample dates for all of the expected scenarios?
Roy Cox

You don't want to use IFERROR until you know that the formula is correct.

Try this

=DATEDIF(A2,IF(ISBLANK(B2),TODAY(),B2),"D")
DATEDIF.xlsx
cfhasan1

ASKER
They second formula with zeros worked.  I'm on a closed network and can't upload files. But here are examples for my second issue:
Start.    End
10/12/15.  11/16/15
11/23/14.   11/17/14
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Rob Henson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cfhasan1

ASKER
Complete Solution
Rob Henson

Thanks for the feedback, glad to help.
Rob Henson

Also, a side point, why are you using DATEDIF for number of days, you will get same result with simple formula for "End Date minus Start Date", ie =B2-A2

To allow for the blanks etc:

=IFERROR(IF(OR(B2="",B2=0),TODAY(),B2)-A2,0)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes