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:



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!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,122
try the following formula:

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,539
Office Version
365
Platform
Windows
golden_eyes

Does this also do what you want?
=IF(E2*E3,DATEDIF(E2,E3,"d"),"")
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
...course you don't really need DATEDIF if you just want the result in days

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

format as general
 

avenkat

New Member
Joined
May 15, 2018
Messages
12
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
 

Forum statistics

Threads
1,089,655
Messages
5,409,554
Members
403,270
Latest member
Man237

This Week's Hot Topics

Top