Revised - Text format string - Date and time patterns - Numbers
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
BCDEFGHIJKLMNOPQRSTU
1
Date and TimeNote some symbols do no work stand alone, hence the extra formatting.
2
SymbolNoteMeaningPresentationFormatFormulaResult
3
Gera designator Textyyyy G GGGG=TEXT( NOW() ; "yyyy G GGGG" )2018
4
yyear Numberyyyy=TEXT( NOW() ; "yyyy" )2018
5
Ylow, not, customyear (related to week of year - w)NumberYYYY=TEXT( NOW() ; "YYYY" )2018
6
uextended year Numberu M d=TEXT( NOW() ; "u M d" )u 5 22
7
M upmonth in yearText & Numberyyyy-M-d M MM MMM MMMM=TEXT( NOW() ; "yyyy-M-d M MM MMM MMMM" )2018-5-22 5 05 May May
8
d lowday in monthNumberyyyy-M-d ddd dddd=TEXT( NOW() ; "yyyy-M-d ddd dddd" )2018-5-22 Tue Tuesday
9
hhour in am/pm (1~12) Numberhh:mm a=TEXT( NOW() ; "hh:mm a" )08:24 PMMidnight+ 1 millisec:12:00 AMMidday + 1 millisec:12:00 PM
10
Hhour in day (0~23) NumberHH:mm=TEXT( NOW() ; "HH:mm" )20:24Midnight+ 1 millisec:00:00Midday + 1 millisec:12:00
11
mupminute in hour NumberHH:mm=TEXT( NOW() ; "HH:mm" )20:24
12
ssecond in minuteNumberHH:mm:ss=TEXT( NOW() ; "HH:mm:ss" )20:24:23
13
S fractional secondNumberHH:mm:ss.SSS=TEXT( NOW() ; "HH:mm:ss.SSS" )20:24:23.100use decimal dot or decimal comma depending on locale!
14
Eday of weekTextyyyy-m-d E EEEE=TEXT( NOW() ; "yyyy-m-d E EEEE" )2018-5-22 Tue Tuesday
15
eday of week (local 1~7)Text & Numberyyyy-m-d e ee eee eeee=TEXT( NOW() ; "yyyy-m-d e ee eee eeee" )2018-5-22 3 03 Tue Tuesday
16
Dlow, not, customDay in yearNumberyyyy DDD=TEXT( NOW() ; "yyyy DDD" )2018 Tue
17
F# of week in month Numberu-L-d E '#' F=TEXT( NOW() ; "u-L-d E '#' F" )#VALUE!
18
wweek of yearNumberYYYY 'week' w=TEXT( NOW() ; "YYYY 'week' w" )2018 week 21
19
Wweek in monthNumberyyyy-MM 'week' W=TEXT( NOW() ; "yyyy-MM 'week' W" )2018-05 week 4
20
aam/pm marker Texthh:mm a=TEXT( NOW() ; "hh:mm a" )08:24 PM
21
k hour in day (1~24) Numberkk:mm=TEXT( NOW() ; "kk:mm" )kk:05Midnight+ 1 millisec:kk:12Midday + 1 millisec:kk:12
22
Khour in am/pm (0~11) NumberKK:mm a=TEXT( NOW() ; "KK:mm a" )KK:05 aMidnight+ 1 millisec:KK:12 aMidday + 1 millisec:KK:12 a
23
znottime zoneTextHH:mm z=TEXT( NOW() ; "HH:mm z" )20:24 GMT
24
Znottime zone (RFC 822)NumberHH:mm Z=TEXT( NOW() ; "HH:mm Z" )20:24 +0000
25
vnottime zone (generic) TextHH:mm v=TEXT( NOW() ; "HH:mm v" )20:24 v
26
Vnottime zone (location)TextHH:mm V=TEXT( NOW() ; "HH:mm V" )20:24 V
27
gJulian day Numberyyyy g=TEXT( NOW() ; "yyyy g" )2018
28
Amilliseconds in dayNumberyyyy A=TEXT( NOW() ; "yyyy A" )2018 A
29
Q quarter in yearText & Numberyyyy Q QQ QQQ QQQQ=TEXT( NOW() ; "yyyy Q QQ QQQ QQQQ" )2018 Q QQ QQQ QQQQ
30
cstand alone day of week Text & Numberyyyy c ccc cccc=TEXT( NOW() ; "yyyy c ccc cccc" )2018 3 Tue Tuesday
31
Lstand alone monthText & Numberyyyy L LL LLL LLLL=TEXT( NOW() ; "yyyy L LL LLL LLLL" )2018 5 05 May May
32
q stand alone quarterText & Numberyyyy q qq qqq qqqq=TEXT( NOW() ; "yyyy q qq qqq qqqq" )2018 q qq qqq qqqq
33
'escape for textDelimiter'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'=TEXT( NOW() ; "'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'" )ABCdEFGHIJKLMNOPQRSTUVWXyZabcdefghijklMnopqrstuvwxyz
34
'' single quoteLiteral''=TEXT( 1 ; "''" )''
35
36
lowD and y are always interpreted as lowercase. when used in the TEXT function.
37
upm is always interpreted as uppercase, except when its meaning can be derived from context as in :mm
38
notz, Z and v, V , Y, and D are not supported
39
customonly supported in custom formats, i.e. NOT in a TEXT function format string
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Loading...
 
 
 
ICU Date and Time
ICU Numbers
ICU formats references
Date and Time (old)
Numbers (old)
Scratch
 
 
Main menu