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… .
In the item Number select Custom.
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.
I am trying to create a formula with the time and day abbreviation ex. 12:00pm Mon. What is the formula for this?
Is this full list of possible date and time format in Excel?
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.
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
Bro, how to format date-time with the milisecond, for ex.:
2014-12-09 17:40:36.973
“973” .. milisecond..
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.
This TEXT formula solved my problem. I was wondering how to display my results.