Custom format of date and time in MS Excel

MS Excel offers many possibilities how display date and time. The custom format can be set in the Format Cells… menu. Do the right-click and select Format Cells… .


RightClick menu in Excel

In the item Number select Custom.

Format Cells menu in Excel

 

Into the field Type: write the date/time codes.

Date format codes

Day

d – day number (0–31)

dd – day number (01–31) / two digits

ddd – day as an abbreviation (mon–sun)

dddd –day name (monday–sunday)

Month

m – month number (1–12)

mm – month number (01–12) / two digits

mmm –month as an abbreviation (Jan–Dec)

mmmm – month name (January–December)

mmmmm – the first letter of month name

Year

yy – year number (00–99) / two digits

yyyy – year number (1900–9999) / four digits

Examples of custom date format

Let’s have the date 3.1.2012

Code Date
dd/mm/yyyy 03/01/2012
mm/dd/yyyy 01/03/2012
d-m-yyyy 3-1-2012
dd-mm-yy 03-01-12
dddd Tuesday
dd. mm. yyyy dddd 03. 01. 2012 Tuesday
mmmm January
d. mmmm yyyy 3. January 2012
mmmm dd, yyyy January 03, 2012
dddd, mmmm dd yyyy Tuesday, January 03 2012
ddmmyy 030112
dd. “text” 03. text

Time format codes

h – hour number (0–23)

hh – hour number (00–23) / two digits

m – minute number (0–59)

mm – minute number (00–59)

s – second number (0–59)

ss – second number (00–59)

AM/PM or am/pm – Convert from 24 Hour to 12 Hour Time

[h] – elapsed time in hours (can be greater than 24, e.g. for sports results)

[mm] – elapsed time in minutes (can be greater than 60, e.g. for sports results)

[ss] – elapsed time in minutes (can be greater than 60, e.g. for sports results)

h:mm:ss.00 – fractions of seconds

Examples of custom time format

Let’s have the time 6:25:31

Code Time
hh.mm 06.25
hhmm 0625
h:mm:ss AM/PM 6:25:31 am
hh “hours and” mm “minutes” 06 hours and 25 minutes
[m] 385 (the number of minutes since 00:00:00)
[s] 23131 (the number of seconds since 00:00:00)

Examples of custom date/time format

Date and time formats can be combined. Notice that month and minute have the same code. Immediately after the hours writes Excel the minutes.

Default date/time: 4.11.2010 6:25

Code Date/Time
hh:mm dd/mm/yyyy 06:25 04/11/2012
dddd hh:mm Thursday 06:25

Note: In other language versions of Excel can be format codes different.

Add a comment »2 comments to this article

  1. Bro, how to format date-time with the milisecond, for ex.:
    2014-12-09 17:40:36.973

    “973″ .. milisecond..

    Reply

    • Hi,

      I think, this cannot be created using format. But there is a solution. Import date-time value as a text in A1 and write the formula =RIGHT(A1,3)&” milliseconds” in the some other cell. It will show the last 3 characters from the text and joins the units.

      Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress