DATEDIF FORMULA FOR POS/NEG AND OPEN FIELDS

[Semperfi4Life83]

I am trying to show the difference in days between two columns and have the return reflect a negative if the date in column "I3" is before the date in column "G3". I have the formula below that returns the number of days for dates in "I3" that are AFTER the date in "G3" and return "OPEN" if no date in "I3".

• =IFERROR(DATEDIF(G3,I3,"D"),"OPEN")

The image below shows the number as OPEN in the first two lines when it should be a negative as it was closed early

Appreciate any help you can offer,

Thanks

 

[JamesCanale]

DATEDIF isn't terribly helpful, when you can use subtraction. It looks like it throws an error when it goes negative.

MrExcelPlayground6.xlsx
	G	H	I	J
3	1/2/2022		1/1/2022	-1
4	1/2/2022			OPEN
5	1/2/2022		1/4/2022	2
Sheet13

Cell Formulas
Range	Formula
J3:J5	J3	=IF(I3<>"",I3-G3,"OPEN")

 

[Saurabhj]

Hi,

If the start_date is greater than the end_date, the result will be #NUM!

use =days(enddate,startdate)

 

[Semperfi4Life83]

DATEDIF isn't terribly helpful, when you can use subtraction. It looks like it throws an error when it goes negative.

MrExcelPlayground6.xlsx
	G	H	I	J
3	1/2/2022		1/1/2022	-1
4	1/2/2022			OPEN
5	1/2/2022		1/4/2022	2
Sheet13

Cell Formulas
Range	Formula
J3:J5	J3	=IF(I3<>"",I3-G3,"OPEN")

James,
Much appreciated, work perfectly!

James