Formula correction

[annadinesh]

Dear Experts

my below formula

Worksheets("EW").Range("C29").Formula = "=Concatenate(""Avail "",Y6, "" on EW Till "",Y7)"

Returns

Avail 0.1 on EW Till 45688

Note:
Y6 contains 10%
Y7 Contains Date

Please check and correct my formula.

Regards


Dinesh

 

[Eric W]

Try:

Worksheets("EW").Range("C29").Formula = "=Concatenate(""Avail "",TEXT(Y6,""#%""), "" on EW Till "",TEXT(Y7,""mm/dd/yyyy""))"

 

[Alex Blakenburg]

@Eric W - I think you made a typo on the % do you want to edit your post ?

 

[Eric W]

Good catch, I edited the formula.

 

[annadinesh]

Try:

Worksheets("EW").Range("C29").Formula = "=Concatenate(""Avail "",TEXT(Y6,""#%""), "" on EW Till "",TEXT(Y7,""mm/dd/yyyy""))"

Dear Experts

Y6 = 7.5%

The Formula

=CONCATENATE("Avail ",TEXT(Y6,"#%"), " Discount on EW Till ",TEXT(Y7,"dd.mm.yyyy"))

returns

Avail 8% Discount on EW Till 31.01.2015 instead of Avail 7.5% Discount on EW Till 31.01.2015

 

[Phuoc]

=CONCATENATE("Avail ",TEXT(Y6,"0.0%"), " Discount on EW Till ",TEXT(Y7,"dd.mm.yyyy"))

 

[Alex Blakenburg]

If you only want 1 decimal point to show and if that is 0 no decimals, then maybe:

=SUBSTITUTE(CONCATENATE("Avail ",TEXT(Y6,"0.0%"), " Discount on EW Till ",TEXT(Y7,"dd.mm.yyyy")),".0%","%")