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" )2019
4
yyear Numberyyyy=TEXT( NOW() ; "yyyy" )2019
5
Ylow, not, customyear (related to week of year - w)NumberYYYY=TEXT( NOW() ; "YYYY" )2019
6
uextended year Numberu M d=TEXT( NOW() ; "u M d" )u 4 25
7
M upmonth in yearText & Numberyyyy-M-d M MM MMM MMMM=TEXT( NOW() ; "yyyy-M-d M MM MMM MMMM" )2019-4-25 4 04 Apr April
8
d lowday in monthNumberyyyy-M-d ddd dddd=TEXT( NOW() ; "yyyy-M-d ddd dddd" )2019-4-25 Thu Thursday
9
hhour in am/pm (1~12) Numberhh:mm a=TEXT( NOW() ; "hh:mm a" )01:51 PMMidnight+ 1 millisec:12:00 AMMidday + 1 millisec:12:00 PM
10
Hhour in day (0~23) NumberHH:mm=TEXT( NOW() ; "HH:mm" )13:51Midnight+ 1 millisec:00:00Midday + 1 millisec:12:00
11
mupminute in hour NumberHH:mm=TEXT( NOW() ; "HH:mm" )13:51
12
ssecond in minuteNumberHH:mm:ss=TEXT( NOW() ; "HH:mm:ss" )13:51:56
13
S fractional secondNumberHH:mm:ss.SSS=TEXT( NOW() ; "HH:mm:ss.SSS" )13:51:56.368use decimal dot or decimal comma depending on locale!
14
Eday of weekTextyyyy-m-d E EEEE=TEXT( NOW() ; "yyyy-m-d E EEEE" )2019-4-25 Thu Thursday
15
eday of week (local 1~7)Text & Numberyyyy-m-d e ee eee eeee=TEXT( NOW() ; "yyyy-m-d e ee eee eeee" )2019-4-25 5 05 Thu Thursday
16
Dlow, not, customDay in yearNumberyyyy DDD=TEXT( NOW() ; "yyyy DDD" )2019 Thu
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" )2019 week 17
19
Wweek in monthNumberyyyy-MM 'week' W=TEXT( NOW() ; "yyyy-MM 'week' W" )2019-04 week 4
20
aam/pm marker Texthh:mm a=TEXT( NOW() ; "hh:mm a" )01:51 PM
21
k hour in day (1~24) Numberkk:mm=TEXT( NOW() ; "kk:mm" )kk:04Midnight+ 1 millisec:kk:12Midday + 1 millisec:kk:12
22
Khour in am/pm (0~11) NumberKK:mm a=TEXT( NOW() ; "KK:mm a" )KK:04 aMidnight+ 1 millisec:KK:12 aMidday + 1 millisec:KK:12 a
23
znottime zoneTextHH:mm z=TEXT( NOW() ; "HH:mm z" )13:51 GMT
24
Znottime zone (RFC 822)NumberHH:mm Z=TEXT( NOW() ; "HH:mm Z" )13:51 +0000
25
vnottime zone (generic) TextHH:mm v=TEXT( NOW() ; "HH:mm v" )13:51 v
26
Vnottime zone (location)TextHH:mm V=TEXT( NOW() ; "HH:mm V" )13:51 V
27
gJulian day Numberyyyy g=TEXT( NOW() ; "yyyy g" )2019
28
Amilliseconds in dayNumberyyyy A=TEXT( NOW() ; "yyyy A" )2019 A
29
Q quarter in yearText & Numberyyyy Q QQ QQQ QQQQ=TEXT( NOW() ; "yyyy Q QQ QQQ QQQQ" )2019 Q QQ QQQ QQQQ
30
cstand alone day of week Text & Numberyyyy c ccc cccc=TEXT( NOW() ; "yyyy c ccc cccc" )2019 5 Thu Thursday
31
Lstand alone monthText & Numberyyyy L LL LLL LLLL=TEXT( NOW() ; "yyyy L LL LLL LLLL" )2019 4 04 Apr April
32
q stand alone quarterText & Numberyyyy q qq qqq qqqq=TEXT( NOW() ; "yyyy q qq qqq qqqq" )2019 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...
Main menu