Compose A Full Excel Date From Parts Elsewhere

  • Thread starter Thread starter robzrob
  • Start date Start date
R

robzrob

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 said:
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?
Click to expand...

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.
 
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.
Click to expand...

Thx - I'll try both.
 
Back
Top