Software

Ask something
Question Locked

Date format

0 helpful
5 replies

by hycm53

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
In Office Excel: how to convert 11.02.2010(in range A1)to 11/02/2010

5 replies

by tobif

MM/dd/yyyy
In some cases, lowercase "mm" may be treated as "minutes", which is not what you want.

(And: the quickest way to open the cell format dialog is Ctrl-1 (that's the digit -one-)

by who am i really

in Excel, in the format cells dialog, you can select one of the available Date presets (examples shown with all numbers or with Month / Day names)

if the desired preset doesn't exist create a custom code in the custom section at the bottom of the same dialog using lower case letters to create the code(s)

ie.
in Excel custom date codes:

mm/dd/yyyy renders to 11/03/2010

mmm./dd/yyyy renders to Nov./03/2010

mmmm/dd/yyyy renders to November/03/2010

ddd. mmmm/dd/yyyy renders to Wed. November/03/2010

dddd mmmm/dd/yyyy renders to Wednesday/November/03/2010

etc.

by tobif

I got used to this distinction ("mm" vs. "MM") many versions ago. But it seems there have been a lot of problems with this, so MS has included some smart guesswork in their newer versions of Office (and I verified this now in Excel VBA 2003)

Even the following will be construed as day-month-hour-minute:
MsgBox (Format(Now(), "dd mm hh mm"))

While this smart context guessing will get things correct in most cases, it has probably added several lines of code to Office, and made it a bit harder for the user, if he really would specifically need the current minute... ("mm" will give you the month!)
A workaround would look something like:
MsgBox (Format(Minute(Now()), "00"))

This discussion is closed to new replies.