Calculate date based on last column used

S

slinger

(Last one, I promise)

I need a formula that will check 8 different columns (O5, T5, Y5, AD5, AI5,
AN5, AS5, AX5). Each column may or may not have a date in it. The columns
are sequential so only the leftmost columns will have a date if all columns
are not filled in. So basically, I need a formula that will count the
columns, but only the ones with information and not all of the columns
between, I'm sure that I need to use the COUNT function but have no idea how
to implement it.

Each column will have a date in it and I need to calculate the day after the
last date listed, (excluding weekends). The difficult part is that in some
rows only 3 or 4 of these columns will have information and in other rows it
may be all 8.

Again, any help would be greatly appreciated.
 
B

Biff

Let's see if we have this straight......

You have 8 cells:

O5, T5, Y5, AD5, AI5, AN5, AS5, AX5

Some, all or none of these 8 cells will have date(s).

The dates (if any) are in ascending order. the date in T5 is greater than
the date in O5. The date in Y5 is greater than the date in T5, etc.

You want to add 1 day to the LAST date entered in those 8 cells but you want
the final result to exclude weekends?

Is this correct?

Biff
 
B

Biff

Try this:

=IF(MAX(O5,T5,Y5,AD5,AI5,AN5,AS5,AX5)=0,"",MAX(O5,T5,Y5,AD5,AI5,AN5,AS5,AX5)+CHOOSE(WEEKDAY(MAX(O5,T5,Y5,AD5,AI5,AN5,AS5,AX5)),1,1,1,1,1,3,2))

Biff
 
B

Biff

You can shorten the formula considerably by using a defined name for the
cells:

=IF(MAX(Range)=0,"",MAX(Range)+CHOOSE(WEEKDAY(MAX(Range)),1,1,1,1,1,3,2))

Biff
 
Top