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 »6 comments to this article

  1. Is this full list of possible date and time format in Excel?

    Reply

  2. Do you know how to display timezone? I have many dates with different timezones. How to convert date to UTC format timezone? Example UTC+4:00
    I tried to use Excel functions.

    Reply

    • You can use macro. I found this:

      Option Explicit

      ‘ Use the PtrSafe attribute for x64 installations
      Private Declare PtrSafe Function FileTimeToLocalFileTime Lib “Kernel32″ (lpFileTime As FILETIME, ByRef lpLocalFileTime As FILETIME) As Long
      Private Declare PtrSafe Function LocalFileTimeToFileTime Lib “Kernel32″ (lpLocalFileTime As FILETIME, ByRef lpFileTime As FILETIME) As Long
      Private Declare PtrSafe Function SystemTimeToFileTime Lib “Kernel32″ (lpSystemTime As SYSTEMTIME, ByRef lpFileTime As FILETIME) As Long
      Private Declare PtrSafe Function FileTimeToSystemTime Lib “Kernel32″ (lpFileTime As FILETIME, ByRef lpSystemTime As SYSTEMTIME) As Long

      Public Type FILETIME
      LowDateTime As Long
      HighDateTime As Long
      End Type

      Public Type SYSTEMTIME
      Year As Integer
      Month As Integer
      DayOfWeek As Integer
      Day As Integer
      Hour As Integer
      Minute As Integer
      Second As Integer
      Milliseconds As Integer
      End Type

      ‘===============================================================================
      ‘ Convert local time to UTC
      ‘===============================================================================
      Public Function UTCTIME(local_time As Date) As Date
      Dim oLocalFileTime As FILETIME
      Dim oUtcFileTime As FILETIME
      Dim oSystemTime As SYSTEMTIME

      ‘ Convert to a SYSTEMTIME
      oSystemTime = DateToSystemTime(local_time)

      ‘ 1. Convert to a FILETIME
      ‘ 2. Convert to UTC time
      ‘ 3. Convert to a SYSTEMTIME
      Call SystemTimeToFileTime(oSystemTime, oLocalFileTime)
      Call LocalFileTimeToFileTime(oLocalFileTime, oUtcFileTime)
      Call FileTimeToSystemTime(oUtcFileTime, oSystemTime)

      ‘ Convert to a Date
      UTCTIME = SystemTimeToDate(oSystemTime)
      End Function

      ‘===============================================================================
      ‘ Convert UTC to local time
      ‘===============================================================================
      Public Function LOCALTIME(utc_time As Date) As Date
      Dim oLocalFileTime As FILETIME
      Dim oUtcFileTime As FILETIME
      Dim oSystemTime As SYSTEMTIME

      ‘ Convert to a SYSTEMTIME.
      oSystemTime = DateToSystemTime(utc_time)

      ‘ 1. Convert to a FILETIME
      ‘ 2. Convert to local time
      ‘ 3. Convert to a SYSTEMTIME
      Call SystemTimeToFileTime(oSystemTime, oUtcFileTime)
      Call FileTimeToLocalFileTime(oUtcFileTime, oLocalFileTime)
      Call FileTimeToSystemTime(oLocalFileTime, oSystemTime)

      ‘ Convert to a Date
      LOCALTIME = SystemTimeToDate(oSystemTime)
      End Function

      ‘===============================================================================
      ‘ Convert a Date to a SYSTEMTIME
      ‘===============================================================================
      Private Function DateToSystemTime(Value As Date) As SYSTEMTIME
      With DateToSystemTime
      .Year = Year(Value)
      .Month = Month(Value)
      .Day = Day(Value)
      .Hour = Hour(Value)
      .Minute = Minute(Value)
      .Second = Second(Value)
      End With
      End Function

      ‘===============================================================================
      ‘ Convert a SYSTEMTIME to a Date
      ‘===============================================================================
      Private Function SystemTimeToDate(Value As SYSTEMTIME) As Date
      With Value
      SystemTimeToDate = _
      DateSerial(.Year, .Month, .Day) + _
      TimeSerial(.Hour, .Minute, .Second)
      End With
      End Function

      Reply

  3. 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

      • This TEXT formula solved my problem. I was wondering how to display my results.

        Reply



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