Solved IF Function w/ dates

October 21, 2013 at 03:07:56
Specs: Windows 7

I have a set of colums with the dates of every step of a given process (the date that each step was accomplished). But for some of the processes there is one step that is not necessary so the cell is either blank, or has N.A. and so the process will automatically go on to the next activity.

Now, I want to study how long each step took to accomplish and I've been using the work days function to see how many days if took.
But I was wondering if its possible to build an IF function that would calculate work days depending on whether there is a date on the previous activity or not. If there is a date then it would calculate normally, if not it would calculate the work days it took between the activity prior to that and this one.

Any help?


See More: IF Function w/ dates

Report •

October 21, 2013 at 08:09:40
Not sure I understand what your looking for.

If you have three Dates something like:

       A              B            C
1) 10/01/2013     10/10/2013    10/21/2013    
2) 10/01/2013                   10/21/2013

Then in D1 a formula like: =IF(B1<>"",B1-A1,C1-A1)
will test to first determine if B1 is Blank,
If B1 is Not Blank then use the Date in B1
If B1 is Blank then use the Date in C1

Is that what your looking to do?


message edited by mmcconaghy

Report •

October 21, 2013 at 08:30:22
Kinda, in this case it solves for when the cell is blank, but not when I have N.A. written on the cells. But I guess what I have to do is manually delete each cell that has N.A. and replace it with a blank space, right?

Also, I'm counting working days, so I'm using networkdays function for each of the values.

Thank you very much!

Report •

October 21, 2013 at 08:50:35
✔ Best Answer
You could add an OR() function to the formula like:


In this we first test to see if B1 is Blank OR If B1 has the Text string N.A.
If True then we use C1
If False we use B1

Personally, I would simply do a Search & Replace and remove all of the N.A.
simply because you will inevitably find some user who will put N/A or NA or
some other combination.
Much simpler to just tell them to leave it blank.





message edited by mmcconaghy

Report •

Related Solutions

October 24, 2013 at 03:51:35
Great! Thank you! :)

Report •

Ask Question