Date calculation between two dates

cfhasan1
cfhasan1 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
Try:

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

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

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

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
Commented:
Start.    End
10/12/15.  11/16/15
11/23/14.   11/17/14

First one should give 35 days.

Second one should give #NUM! error as Start is later than End but the IFERROR should convert it to 0

Author

Commented:
Complete Solution
Rob HensonFinance Analyst

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

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)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial