We help IT Professionals succeed at work.

Date calculation between two dates

75 Views
Last Modified: 2018-12-20
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 .
Comment
Watch Question

Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Try:

=IFERROR(DATEDIF(A2,IF(B2="",TODAY(),B2),"d"),0)
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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)

Author

Commented:
Cells remain blank with this formula. Also remains blank when start is greater than end date
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Can you upload a file with some sample dates for all of the expected scenarios?
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
Finance Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Complete Solution
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Thanks for the feedback, glad to help.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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)