Using an IF statement and calculating days between 2 dates

golden_eyes

New Member
Joined
Jun 28, 2006
Messages
4
Excel version: Excel 2000

I'll explain what I'm trying to do up front, then explain a bit about the format of the spreadsheet I'm working with:

I am trying to calculate the number of days between two dates, but only if there are dates entered in certain cells. If one or both of the cells are blank, I do not want Excel to calculate the difference between two blank cells and return a value of 0.

So here is some information on the spreadsheet:

I have a spreadsheet that I am using for a file review. The spreadsheet has a "yes" column, a "no" column, an "N/A" column, and a "date" column - see the screenshot below:

20060628_example_excel.jpg


When the answer in the "yes" column (B) is "yes," there should be a date in the date column (E).

Originally, I was used the DATEDIF function to calculate the difference between two dates - for example:
=DATEDIF(E2,E3,"d")

The problem is that if both E2 and E3 are blank, Excel returns a result of 0 instead of just leaving the cell blank. This is a problem because ultimately I want to calculate the average of the days elapsed between pairs of dates, and sometimes the number of days elapsed will be 0, so I want to include 0s in the average calculation.

So my question again is - how do I calculate the number of days elapsed between two dates only when there is a value in the date column for both cells? I thought it might be possible to use an IF statement (like IF B2=Yes and B3=Yes, then calculate the number of days between E2 and E3). However, there might be a better way to do it, and I don't know how to write such an IF statement at any rate, so I humbly ask for your help.

Hopefully all of that makes sense. Thanks in advance!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
try the following formula:

D3=IF(AND(B2="yes",B3="yes"),DATEDIF(E2,E3,"d"),"")
 
Upvote 0
golden_eyes

Does this also do what you want?
=IF(E2*E3,DATEDIF(E2,E3,"d"),"")
 
Upvote 0
...course you don't really need DATEDIF if you just want the result in days

=IF(E2*E3,E3-E2,"")

format as general
 
Upvote 0
Hi All,
I have a similar problem so choosing to use this thread to get some clarity.
So in my case I have 4 columns A, B, C , D all containing dates for various stages of a process. There are few dates missing in each /either columns.
I need to calculate the day differences between these stages in succession ( B-A, C-B, D-C). I used DAYS for the ones that have dates but for the ones that donot have dates, I tried using
=IF(B2>0,B2-A2,TODAY()-B2)
For instance if Row 1 is 2/11/2017 , """,""",""" ,
I get a weird value for C-B(since both are 0)

How else could we get a cascading effect for this problem ?

Any pointers helpful

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top