Use of DATEIF Function

[Marcie Be]

I am using the DATEIF function to stop counting the numbers of days open when a closed date is input in another cell.
I also need the formula to work when the cell with the creation date is blank.
Here is my Formula
AA=DATEDIF($Z4,IF(H4>0,H4,TODAY()),"D")

If Cell $Z4 is empty i.e. there is no creation date the cell AA should be blank/empty

Should I use the OR Function? If so how?

 

[JackDanIce]

Welcome to the board. Try:

=IF($Z4="","",DATEDIF($Z4,IF($H4>0,TODAY(),$H4),"D")

 

[Marcie Be]

Thank you Jack, for your prompt reply very helpful. Not quite resolved yet, see comments below

=IF($Z4="","",DATEDIF($Z4,IF($H4>0,TODAY(),$H4),"D")

I was one column to the right so the formula I edited too

=IF($AA4="","",DATEDIF($AA4,IF($H4>0,TODAY(),$H4),"D"))

I added in the extra closed bracket on the end

I get a #NUM! error

Something not quite right. Almost but not quite

 

[JackDanIce]

Try:

=IF($AA4="","",DATEDIF($AA4,IF($H4>0,TODAY()),"D"))

which is similar to your original formula, nothing specified for the FALSE part within the IF statement.

 

[Marcie Be]

Try:

=IF($AA4="","",DATEDIF($AA4,IF($H4>0,TODAY()),"D"))

which is similar to your original formula, nothing specified for the FALSE part within the IF statement.

Thanks again

This now works if there is a data in $H4
I need the formula to work when $H4 is empty as well. A date will not be entered into $H4 until the task is complete.

 

[JackDanIce]

DATEDIF needs 2 dates to calculate the difference in dates. You're using AA4 and Today(), IF H4 is > 0.
If H4 is not greater than 0, what date should it use instead of Today()?

 

[Marcie Be]

what date should it use instead of Today()?

If H4 is not greater than Zero this means the task is not complete. The days should keep counting, until task is complete and a date is entered into H4. Once a date is entered into H4 the count should stop as the task is now complete.
Cell $AB4 is counting the number of days the task is open from Creation Date cell $AA4 until task is complete. Completion date entered into cell $H4

 

[JackDanIce]

Try:

=IF($H4,IFEERROR(DATEDIF($AA4,TODAY(),"D"),""),$AB4)

 

[Marcie Be]

=IF($H4,IFEERROR(DATEDIF($AA4,TODAY(),"D"),""),$AB4)

That produces a 0 in cell $AB4. When cells $AA4 & $H4 are empty
$AB4 is also 0 when date entered into $AA4 this is wrong. When a date is input into $AA4, the number of days open should be counted in cell $AB4

The above formula returned a date of 00/01/1900 which I converted to a number by formatting it to general which then converted it to a 0.
Ideally if the formula produces a 0 then I need it to replace the zero with a space so that the cell appears blank, when $AA4 the creation date cell is empty.

Thanks again for all your help.

 

[JackDanIce]

You're welcome, glad you resolved it