Combining Text and Formatted Date

  • Thread starter Thread starter Gary Elsner
  • Start date Start date
G

Gary Elsner

I have a date which is formated as "13-Mar". (That is, I
need to see only the day and the Month and not the year.)

I want to combine this formatted date with the
word "Birthday" using a vba statement similar to:

Worksheets("Output").Cells(CurRow, 2) = "Birthday: " + Str
(Worksheets("BRWCInfo").Cells(CurRow, 3))

But it says I have a "type mismatch".

Help...I've tried a lot of different approaches. And
can't get the result to equal "Birthday = 13-Mar".

I will appreciate your help very much,

/ Gary E.
 
Jr,

When I try to run this statement it says "sub or function
not define" and is referring to "TEXT".

Huhh....what to do? I'm using Excel 2002 with the XP Pro
operating system.

More help will be greatly appreciated.

Below is the actual statement I tried to run -- should the
& be in it or the old fashioned + sign?

Worksheets("Output").Cells(CurRow, 2) = "Birthday: " & Text
(Month(Worksheets("Output").Cells(CurRow, 3)), "mmm") & "-
" & Text(Day(Worksheets("Output").Cells(CurRow, 3)), "dd")
 
Ron,

Fantastic! Your suggestion works like a charm.

One more question --- I would really like the birtday to
be formatted as: "Mar 13" not as 13-Mar as the formatting
allows.

How can I do that?

I tried the previous suggestion of using the "Text"
function, but I can't get it work at all.

I will appreciate your help greatly!!

/ Gary E.
Click to expand...


In VBA you can use the Format function. But first you must ensure that the
13-Mar expression is an Excel date. And then use that in the Format
expression.

For clarity, I define an extra variable called dt.

So this should work in the present situation where 13-Mar can be either text,
or a true date, but I have not tested it extensively to ensure that it will
work in all situations:

===================
Dim dt As Date
dt = Worksheets("BRWCInfo").Cells(CurRow, 3).Text

Worksheets("Output").Cells(CurRow, 2) = "Birthday: " + _
Format(dt, "mmm d")
========================




--ron
 
Back
Top