Hello

I've got a formula which returns the Worksheet name to M11 (that name
will always be a month), and I've got the year in M3. How can I get
01/MM/YY from these cells?

The DateValue function converts text to a date if the text resembles a date.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Data Options add-in: Color or Delete or Insert: specific rows/dates/random data)



"robzrob" <(E-Mail Removed)>
wrote in message
news:56b4dda2-ff92-4b92-ae5c-(E-Mail Removed)...
> Hello
>
> I've got a formula which returns the Worksheet name to M11 (that name
> will always be a month), and I've got the year in M3. How can I get
> 01/MM/YY from these cells?

"robzrob" <(E-Mail Removed)> wrote:
> I've got a formula which returns the Worksheet name to M11
> (that name will always be a month), and I've got the year
> in M3. How can I get 01/MM/YY from these cells?

It is tempting to suggested using DATEVALUE as follows:

=DATEVALUE(M11 & M3)

formatted as Date with an appropriate Type.

That assumes that the month name in M11 is of the form December or Dec, and
the year in M3 is 4 digits.

However, the success of DATEVALUE depends on whether M11&M3 is recognized as
a date on your system. Alternatively, you could use:

=DATE(M3,MATCH(M11,
{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),1)

Again, M3 should be 4 digits to avoid misinterpretation. And if M11 is the
full month name (e.g. December), substitute the 3-character abbreviations
above appropriately, and localize as needed.

On Dec 28 2011, 1:04*am, "joeu2004" <joeu2...@foo.bar> wrote:
> "robzrob" <robz...@hotmail.com> wrote:
> > I've got a formula which returns the Worksheet name to M11
> > (that name will always be a month), and I've got the year
> > in M3. *How can I get 01/MM/YY from these cells?
>
> It is tempting to suggested using DATEVALUE as follows:
>
> =DATEVALUE(M11 & M3)
>
> formatted as Date with an appropriate Type.
>
> That assumes that the month name in M11 is of the form December or Dec, and
> the year in M3 is 4 digits.
>
> However, the success of DATEVALUE depends on whether M11&M3 is recognizedas
> a date on your system. *Alternatively, you could use:
>
> =DATE(M3,MATCH(M11,
> {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0*),1)
>
> Again, M3 should be 4 digits to avoid misinterpretation. *And if M11 isthe
> full month name (e.g. December), substitute the 3-character abbreviations
> above appropriately, and localize as needed.

Thx - I'll try both.

On Dec 27 2011, 10:55*pm, "Jim Cone" <james.cone...@comcast.netXxx>
wrote:
> The DateValue function converts text to a date if the text resembles a date.
> --
> Jim Cone
> Portland, Oregon USA *.http://www.mediafire.com/PrimitiveSoftware*.
> (Data Options add-in: Color or Delete or Insert: specific rows/dates/random data)
>
> "robzrob" <robz...@hotmail.com>
> wrote in messagenews:56b4dda2-ff92-4b92-ae5c-(E-Mail Removed)...
>
>
>
> > Hello
>
> > I've got a formula which returns the Worksheet name to M11 (that name
> > will always be a month), and I've got the year in M3. *How can I get
> > 01/MM/YY from these cells?- Hide quoted text -
>
> - Show quoted text -

Thx - I'll try and see.