Number of Days between date but if cell has "na" go to next date

My spread sheet consists of a column with a date and a column next to it with a formula that looks at that date and the date in front of it and returns the number of days between the two, this is repeated over 12 columns. Example Column A row 8 has a date, Column B row 8 has a date, column K has a formula =NETWORKDAYS(A8,B8) which works returning number of working days. My issue is I have 12 colums with dates in each and each one has a column with a formula next to it. Each column with a formula looks at the two dates in front of it to return a number of days, but some times the spread sheet will have 6 of the columns with "na" in the cell instead of a date. The first 5 columns always have a date and sometime the next six columns have "na" and the last column always has a date. If the last column can't return a number of days because the date in front of it is "na" I want it to first look at the next 5 or 6 columns that could have a date and if "na" keep looking until it finds a date to calculate number of days from last entered date. Did I make this very confusing.


can anyone help?

how about nested ifs?

ie

Formula:

=NETWORKDAYS(A8,IF(B8<>"na",B8,IF(D8<>"na",D8,if(...,"na"))))

?

Master PCN Data.xlsmSimarui,

I tried it but no luck, I'm sure it is me!
I attached the file for you to look at if that is ok.
The cell in RED looks at the first two cells in Yellow in front of it (EW12 & EJ12) the way I have it setup currently is if their is a date in EJ12 its happy and gives me a number of days between dates, if no date is in cell EJ12 or "na" is their I want it to look at DW12 the next one back and keep going until it finds a date (in this case Orange cell BJ12) and puts a number of Networkdays in the Red cell FI12. So it is looking at the date in cell EW12 and BJ12 and giving a number of days.

Hope this makes sense.
Jim

Try this:

=NETWORKDAYS(LOOKUP(2,1/(ISNUMBER(J12:EJ12)*(J$6:EJ$12<>"")),J12:EJ12),EW12)

Did not work! Part of your formula has me confused =NETWORKDAYS(LOOKUP(2,1/(ISNUMBER(J12:EJ12)*(J$6:EJ$12<>"")),J12:EJ12),EW12) should the J$6:EJ$12 be J$12:EJ$12? I tried changing the 6 to a 12 but still did not work.

Originally Posted by Bikeman

Did not work! Part of your formula has me confused =NETWORKDAYS(LOOKUP(2,1/(ISNUMBER(J12:EJ12)*(J$6:EJ$12<>"")),J12:EJ12),EW12) should the J$6:EJ$12 be J$12:EJ$12? I tried changing the 6 to a 12 but still did not work.

It should be (J$6:EJ$6<>"")instead of (J$6:EJ$12<>"")