Hello! 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?

Thanks,

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/2013Then 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 C1Is that what your looking to do?

MIKE

message edited by mmcconaghy

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!

You could add an OR() function to the formula like: =IF(OR(B1="N.A.",B1=""),C1-A1,B1-A1)

In this we first test to see if B1 is Blank

ORIf B1 has the Text string N.A.

If True then we use C1

If False we use B1Personally, 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.EDIT ADDED:

For NETWORKDAYS()

=IF(B1<>"",NETWORKDAYS(A1,B1),NETWORKDAYS(A1,C1))

MIKE

message edited by mmcconaghy

Great! Thank you! :)

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History