MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Date and time display custom format ?

  Asked By: Madeline    Date: Jan 18    Category: MS Office    Views: 1168

Assume cell A1 is currently formatted to display a date & time in the format
YYYY-MM-DD HH:MM:SS. The date:
2006-05-13 16:30:45
displays fine.

Why can't I use custom format this to YYYYMMDD:HHMM ?

When I try, I get an #NUM! error. I would appreciate having the following

2006-04-20 08:45:44
2006-03-07 09:07:02

displayed as:



7 Answers Found

Answer #1    Answered By: Sarah Campbell     Answered On: Jan 18

I had no problem doing this, using cut and paste from your post below,
even using the specific dates mentioned. Does the cell  contain a formula?

Answer #2    Answered By: Gloria Cook     Answered On: Jan 18

Try this

Answer #3    Answered By: Kaysah Mohammad     Answered On: Jan 18

Sorry this is brief, quite busy at the moment.

Try setting the custom  format of the cell  to:


Answer #4    Answered By: Gus Jones     Answered On: Jan 18

That is about all I can say. When *I* use YYYYMMDD:HHMM, it just
doesn't work for me. I am using Excel 2000. Maybe a reinstall is needed?
But when I used yyyymmdd:hhmm, it does work! What is the difference? I am
at a loss here. I do have two addins for Excel, one is a unit converter
(convert sizes/amounts to other sizes, such as kilometers to miles), and the
other addin is the ASAP Utilities. Could one (or both) be preventing the
"YYYYMMDD:HHMM" from working?
I am perfectly happy to use yyyymmdd:hhmm, just don't understand why the
capitalized version of that didn't work?

Answer #5    Answered By: Hisa Yoshida     Answered On: Jan 18

In newer versions of Excel, Excel reformats the capitalized version to
lowercase, which is why David said it worked for him. After you questioned
the capitalized versus lowercase, I took a peak. My Excel 2003 reformatted
to yyyymmdd:hhmm

From Help - format  codes:

Days, months, and years If you use "m" immediately after the "h" or "hh"
code or immediately before the "ss" code, Microsoft Excel displays minutes
instead of the month.

To display  Use this code
Months as 1-12 m
Months as 01-12 mm
Months as Jan-Dec mmm
Months as January-December mmmm
Months as the first letter of the month mmmmm
Days as 1-31 d
Days as 01-31 dd
Days as Sun-Sat ddd
Days as Sunday-Saturday dddd
Years as 00-99 yy
Years as 1900-9999 yyyy

Hours, minutes, and seconds To display Use this code
Hours as 0-23 H
Hours as 00-23 hh
Minutes as 0-59 m
Minutes as 00-59 mm
Seconds as 0-59 s
Seconds as 00-59 ss
Hours as 4 AM h AM/PM
Time as 4:36 PM h:mm AM/PM
Time as 4:36:03 P h:mm:ss A/P
Elapsed time  in hours; for example, 25.02 [h]:mm
Elapsed time in minutes; for example, 63:46 [mm]:ss
Elapsed time in seconds [ss]
Fractions of a second h:mm:ss.00

AM and PM If the format contains an AM or PM, the hour is based on the
12-hour clock, where "AM" or "A" indicates times from midnight until noon
and "PM" or "P" indicates times from noon until midnight. Otherwise, the
hour is based on the 24-hour clock. The "m" or "mm" code must appear
immediately after the "h" or "hh" code or immediately before the "ss" code;
otherwise, Microsoft Excel displays the month instead of minutes.

Answer #6    Answered By: Courtney Hughes     Answered On: Jan 18

Certainly, when you look at the date  formats using the custom  formatter, they
are all lower case. Possibly the case insensitivity is a reasonably new

However, I just plugged the upper-case version into Excel 2000 9.0.6926 SP-3 and
it worked fine.

I don't have either of the addins installed. Maybe they are interfering in some

Answer #7    Answered By: Hiroshi Yoshida     Answered On: Jan 18

I didn't look back at the format. You're correct, my format  has
been squashed to lower case in both 2003 and 2000.

Didn't find what you were looking for? Find more on Date and time display custom format ? Or get search suggestion and latest updates.