Link to home
Start Free TrialLog in
Avatar of cfhasan1
cfhasan1Flag 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 .
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Try:

=IFERROR(DATEDIF(A2,IF(B2="",TODAY(),B2),"d"),0)
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)
Avatar of cfhasan1

ASKER

Cells remain blank with this formula. Also remains blank when start is greater than end date
Can you upload a file with some sample dates for all of the expected scenarios?
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
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
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Complete Solution
Thanks for the feedback, glad to help.
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)