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:
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!
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:
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!