cfhasan1

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 .

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)

=IFERROR(DATEDIF(A2,IF(OR(

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

Try this

=DATEDIF(A2,IF(ISBLANK(B2)

DATEDIF.xlsx

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

Start. End

10/12/15. 11/16/15

11/23/14. 11/17/14

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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)

To allow for the blanks etc:

=IFERROR(IF(OR(B2="",B2=0)

=IFERROR(DATEDIF(A2,IF(B2=