Date formating

[singh]

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.

 

[Pete_UK]

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

 

[singh]

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]

Glad to help - thanks for feeding back.

Pete

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..








- Show quoted text -