# DATEDIF FORMULA FOR POS/NEG AND OPEN FIELDS

#### Semperfi4Life83

##### New Member
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

Thanks

#### Attachments

• 1642172489490.png
8 KB · Views: 8

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
DATEDIF isn't terribly helpful, when you can use subtraction. It looks like it throws an error when it goes negative.

MrExcelPlayground6.xlsx
GHIJ
31/2/20221/1/2022-1
41/2/2022OPEN
51/2/20221/4/20222
Sheet13
Cell Formulas
RangeFormula
J3:J5J3=IF(I3<>"",I3-G3,"OPEN")

Hi,

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

use =days(enddate,startdate)

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

MrExcelPlayground6.xlsx
GHIJ
31/2/20221/1/2022-1
41/2/2022OPEN
51/2/20221/4/20222
Sheet13
Cell Formulas
RangeFormula
J3:J5J3=IF(I3<>"",I3-G3,"OPEN")

James,
Much appreciated, work perfectly!

James

Replies
1
Views
283
Replies
3
Views
261
Replies
2
Views
278
Replies
7
Views
346
Replies
1
Views
159

1,207,011
Messages
6,076,144
Members
446,187
Latest member
LMill

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back