Comparing Cells and Displaying Data

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" <[email protected]> wrote in message
news:[email protected]...
>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