I am needing to calculate a cell based on the values that may or may not
reside in other cells. Currently I have two columns. The first is "Original
Need Date" this date once entered never changes. The second is "Updated Need
Date" and this changes from time to time. We have agreed to enter the text
in the "Updated Need Date" field in MM/DD/YY format followed by a semi colon
and then any text the user wants after the semi colon. This allows us to
enter a new date or text.

I have tried two different formulas and neither of them work in all
situations.

Formula 1: "=IF(B1=0,A1,B1)"

- Issue #1 - If the user enters anything other than MM/DD/YY in the second
column my formula does not work properly because it will show the additional
data such as "04/21/05; updated" which breaks formulas down the line where I
am looking for dates to find out how many need dates are in a particular
month such as March.
- Issue #2 - If the user has no value in either column A or column B, then
the date comes out as 1/0/1900 which is wrong as well.

Formula 2: "=IF(LEFT(TRIM(B1),10)=0,A1,B1)" - What I am trying to do is trim
the value in column B to just the date and then do the comparison and display
the value in column C

- Issue #1 - Gives the same results as the first query and does not trim the
value in column B
- Issue #2- If the user has no value in either column A or column B, then
the date comes out as 1/0/1900 which is wrong as well.


Column A Column B Column C
Orig Need Date Updated Need Date New Forecast Need Date
09/03/04 09/03/04
07/29/05 01/21/05 01/21/05
07/05/05 04/21/05; updated 04/21/05
08/01/05 03/01/05; 06/01/05 03/01/05

So, what I am looking to do is put the first 10 characters (MM/DD/YYYY)
value of Column B into Column C if there is a value in Column B, else if
there is no value in Column B, put the value from Column A into Column C, and
last but not least, if there is no value in either column, Column C should
also be blank.

Thank you,
Keith Brown

In C2

=IF(B2<"",(LEFT(TRIM(B2),10)),IF(A2<"",A2,""))

Ian

"Keith Brown" wrote in message
...
I am needing to calculate a cell based on the values that may or may not
reside in other cells. Currently I have two columns. The first is
"Original
Need Date" this date once entered never changes. The second is "Updated
Need
Date" and this changes from time to time. We have agreed to enter the
text
in the "Updated Need Date" field in MM/DD/YY format followed by a semi
colon
and then any text the user wants after the semi colon. This allows us to
enter a new date or text.

I have tried two different formulas and neither of them work in all
situations.

Formula 1: "=IF(B1=0,A1,B1)"

- Issue #1 - If the user enters anything other than MM/DD/YY in the second
column my formula does not work properly because it will show the
additional
data such as "04/21/05; updated" which breaks formulas down the line where
I
am looking for dates to find out how many need dates are in a particular
month such as March.
- Issue #2 - If the user has no value in either column A or column B, then
the date comes out as 1/0/1900 which is wrong as well.

Formula 2: "=IF(LEFT(TRIM(B1),10)=0,A1,B1)" - What I am trying to do is
trim
the value in column B to just the date and then do the comparison and
display
the value in column C

- Issue #1 - Gives the same results as the first query and does not trim
the
value in column B
- Issue #2- If the user has no value in either column A or column B, then
the date comes out as 1/0/1900 which is wrong as well.


Column A Column B Column C
Orig Need Date Updated Need Date New Forecast Need Date
09/03/04 09/03/04
07/29/05 01/21/05 01/21/05
07/05/05 04/21/05; updated 04/21/05
08/01/05 03/01/05; 06/01/05 03/01/05

So, what I am looking to do is put the first 10 characters (MM/DD/YYYY)
value of Column B into Column C if there is a value in Column B, else if
there is no value in Column B, put the value from Column A into Column C,
and
last but not least, if there is no value in either column, Column C should
also be blank.

Thank you,
Keith Brown