Due date changes when new value is inputted in a different column
 

I have this if statement
=IF(I4:I150<=10101,"",DATE(YEAR(I4:I150),MONTH(I4: I150)+G4:G150,DAY(I4:I150)))
and it works great when it looks at this one column. It returns the answer I
want. However, I need it to look at additional columns that will superceed
the previous one without erasing their date and still show the result in the
original column.
Column "F" shows the result. Columns "I-Z" give the date superceeding each
occurrance.
i.e. "F" returns a date based on column "G" and "I". When a new date is put
in "J" I want column "F" to show the update.

Due date changes when new value is inputted in a different column
 

It's long but it seems to work:
=MID(CONCATENATE(G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2, R2,S2,T2,U2,V2,W2,X2,Y2,Z2),1,5)*1

WHy it works:
When you concatenate cells, excel treats them as text. This means each date
will actually be read as the date serial in this concatenation. Since date
serials are always 5 digits you pull the left most five and that means that
as the users fill in dates, the first cell refrence to have a value will get
precedence. If you wanted to give precedence to items more to the right (Z
trumps y, then just reverse the list.)

Multiplying it by one turns the date serial from text to numeric, so when
you format as date it will show a classic date instead of a serial.
*NOTE*
This breaks down if a user enters a value other then a date in a cell.

"John" wrote in message
...
I have this if statement
=IF(I4:I150<=10101,"",DATE(YEAR(I4:I150),MONTH(I4: I150)+G4:G150,DAY(I4:I150)))
and it works great when it looks at this one column. It returns the answer
I
want. However, I need it to look at additional columns that will superceed
the previous one without erasing their date and still show the result in
the
original column.
Column "F" shows the result. Columns "I-Z" give the date superceeding each
occurrance.
i.e. "F" returns a date based on column "G" and "I". When a new date is
put
in "J" I want column "F" to show the update.

Due date changes when new value is inputted in a different col
 

Many Thanks!!!! You have no idea how helpful this has been. Thank you.

"Oorang" wrote:

It's long but it seems to work:
=MID(CONCATENATE(G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2, R2,S2,T2,U2,V2,W2,X2,Y2,Z2),1,5)*1

WHy it works:
When you concatenate cells, excel treats them as text. This means each date
will actually be read as the date serial in this concatenation. Since date
serials are always 5 digits you pull the left most five and that means that
as the users fill in dates, the first cell refrence to have a value will get
precedence. If you wanted to give precedence to items more to the right (Z
trumps y, then just reverse the list.)

Multiplying it by one turns the date serial from text to numeric, so when
you format as date it will show a classic date instead of a serial.
*NOTE*
This breaks down if a user enters a value other then a date in a cell.

"John" wrote in message
...
I have this if statement
=IF(I4:I150<=10101,"",DATE(YEAR(I4:I150),MONTH(I4: I150)+G4:G150,DAY(I4:I150)))
and it works great when it looks at this one column. It returns the answer
I
want. However, I need it to look at additional columns that will superceed
the previous one without erasing their date and still show the result in
the
original column.
Column "F" shows the result. Columns "I-Z" give the date superceeding each
occurrance.
i.e. "F" returns a date based on column "G" and "I". When a new date is
put
in "J" I want column "F" to show the update.

Due date changes when new value is inputted in a different col
 

Oorang,
I have one additional request. I want the cell to be empty until I use the
columns. How do I keep the error (#VALUE!) from showing up?

"John" wrote:

Many Thanks!!!! You have no idea how helpful this has been. Thank you.

"Oorang" wrote:

It's long but it seems to work:
=MID(CONCATENATE(G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2, R2,S2,T2,U2,V2,W2,X2,Y2,Z2),1,5)*1

WHy it works:
When you concatenate cells, excel treats them as text. This means each date
will actually be read as the date serial in this concatenation. Since date
serials are always 5 digits you pull the left most five and that means that
as the users fill in dates, the first cell refrence to have a value will get
precedence. If you wanted to give precedence to items more to the right (Z
trumps y, then just reverse the list.)

Multiplying it by one turns the date serial from text to numeric, so when
you format as date it will show a classic date instead of a serial.
*NOTE*
This breaks down if a user enters a value other then a date in a cell.

"John" wrote in message
...
I have this if statement
=IF(I4:I150<=10101,"",DATE(YEAR(I4:I150),MONTH(I4: I150)+G4:G150,DAY(I4:I150)))
and it works great when it looks at this one column. It returns the answer
I
want. However, I need it to look at additional columns that will superceed
the previous one without erasing their date and still show the result in
the
original column.
Column "F" shows the result. Columns "I-Z" give the date superceeding each
occurrance.
i.e. "F" returns a date based on column "G" and "I". When a new date is
put
in "J" I want column "F" to show the update.