Hi All

Excel is not pulling the date in proper format while applying the date cell
in a formula.
ER:
ColumnA ColumnB ColumnC
Amount Date Settled
250 4/9/2008 Amount 250 Settled on 39547

Column C formula is =CONCATENATE("Amount"," ",A2," ","Settled on ",B2)

I need date 04/09/08 in C column not 39547. Formula should give me Amount
250 settled on 04/09/08.

Is there any function to resolve this?

Thanks in advance.

You can do it like this:

="Amount "&A2&" Settled on "&TEXT(B2,"mm/dd/yyyy")

You don't need to use CONCATENATE - the & operator does the same.

Hope this helps.

Pete

On Apr 10, 10:24*am, singh wrote:
Hi All

Excel is not pulling the date in proper format while applying the date cell
in a formula.
ER:
*ColumnA * * * ColumnB * * *ColumnC
*Amount * * * * *Date * * * * * * Settled
* 250 * * * * * * *4/9/2008 * * *Amount 250 Settled on 39547

Column C formula is =CONCATENATE("Amount"," ",A2," ","Settled on ",B2)

* I need date 04/09/08 in C column not 39547. Formula should give me Amount
250 settled on 04/09/08.

Is there any function to resolve this?

Thanks in advance.

Thanks a bunch Pete

I never thought about this. It is the simple way. I was trying doing with
macro.
Code
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
Range("C2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE("Amount"," ",A2," ","Settled on
",B2)"

This is working. But yours is really really easy.

Simplicity is best.. Thanks to you once again..


"Pete_UK" wrote:

You can do it like this:

="Amount "&A2&" Settled on "&TEXT(B2,"mm/dd/yyyy")

You don't need to use CONCATENATE - the & operator does the same.

Hope this helps.

Pete

On Apr 10, 10:24 am, singh wrote:
Hi All

Excel is not pulling the date in proper format while applying the date cell
in a formula.
ER:
ColumnA ColumnB ColumnC
Amount Date Settled
250 4/9/2008 Amount 250 Settled on 39547

Column C formula is =CONCATENATE("Amount"," ",A2," ","Settled on ",B2)

I need date 04/09/08 in C column not 39547. Formula should give me Amount
250 settled on 04/09/08.

Is there any function to resolve this?

Thanks in advance.

Glad to help - thanks for feeding back.

Pete

On Apr 10, 11:35*am, singh wrote:
Thanks a bunch Pete

I never thought about this. It is the simple way. I was trying doing with
macro.
Code
Columns("A:A").Select
* * Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
* * * * FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
Range("C2").Select
* * ActiveCell.FormulaR1C1 = "=CONCATENATE("Amount"," ",A2," ","Settled on
",B2)"

This is working. But yours is really really easy.

Simplicity is best.. *Thanks to you once again..



"Pete_UK" wrote:
You can do it like this:

="Amount "&A2&" Settled on "&TEXT(B2,"mm/dd/yyyy")

You don't need to use CONCATENATE - the & operator does the same.

Hope this helps.

Pete

On Apr 10, 10:24 am, singh wrote:
Hi All

Excel is not pulling the date in proper format while applying the date cell
in a formula.
ER:
*ColumnA * * * ColumnB * * *ColumnC
*Amount * * * * *Date * * * * * * Settled
* 250 * * * * * * *4/9/2008 * * *Amount 250 Settled on 39547

Column C formula is =CONCATENATE("Amount"," ",A2," ","Settled on ",B2)

* I need date 04/09/08 in C column not 39547. Formula should give me Amount
250 settled on 04/09/08.

Is there any function to resolve this?

Thanks in advance.- Hide quoted text -

- Show quoted text -