You can use a date value from your spreadsheet and alter the way it looks in your text message. The following examples work with a spreadsheet like this.
A | B | C | D | |
---|---|---|---|---|
1 | name | date | mobile | balance |
2 | Joe | 26/02/2021 15:11:29 | 078 00000000 | 12.50 |
^^date^^
Formatting instructions are added inside curly brackets { }. For date and time fields the instruction always starts with the letter d followed by a colon : . This is then followed by the actual format instruction. For example:^^date{d:M}^^
This tells ExcelSend the field is a date field and the M is the requested output format in the text message.Straight merge - no formatting:
^^date^^ = 2/26/2021 3:11:29 PM
Date only:
^^date{d:yyyy-MM-dd}^^ = 2021-02-26
^^date{d:MM/dd/yyyy}^^ = 02/26/2021
^^date{d:M}^^ = February 26
^^date{d:Mx}^^ = February 26th
Time only:
^^date{d:h:mm}^^ = 3:11
^^date{d:hh:mm}^^ = 03:11
^^date{d:HH:mm}^^ = 15:11
^^date{d:h:mmtt}^^ = 3:11PM
^^date{d:t}^^ = 3:11 PM
Here is a table of common specifiers that can be used for a datetime value:
Specifier | Type | Example | Output |
d | Short date | {d:d} | 26/10/2021 |
D | Long date | {d:D} | Friday February 26, 2021 |
t | Short time | {d:t} | 3:11 PM |
T | Long time | {d:T} | 3:11:29 PM |
f | Full date & time | {d:f} | Friday February 26, 2021 3:11 PM |
F | Full date & time (long) | {d:F} | Friday February 26, 2021 3:11:29 PM |
g | Default date & time | {d:g} | 26/12/2021 3:11 PM |
G | Default date & time (long) | {d:G} | 26/12/2021 3:11:29 PM |
M | Month day pattern | {d:M} | February 26 |
Mx | Month day with ordinal indicator st/nd/rd/th | {d:Mx} | February 26th |
s | Sortable date string | {d:s} | 2021-02-26T15:11:29 |
u | Universal sortable, local time | {d:u} | 2021-02-26 15:11:29Z |
U | Universal sortable, GMT | {d:U} | February 26, 2021 3:11:29 AM |
Y | Year month pattern | {d:Y} | February, 2021 |
You can customize your format using a combination of the specifiers in the table below.
Specifier | Type | Example | Output |
dd | Day | {d:dd} | 26 |
x | Day with ordinal indicator st/nd/rd/th | {d:ddx} | 26th |
ddd | Day name | {d:ddd} | Fri |
dddd | Full day name | {d:dddd} | Friday |
f, ff, … | Second fractions | {d:fff} | 932 |
gg, … | Era | {d:gg} | A.D. |
hh | 2 digit hour | {d:hh} | 03 |
HH | 2 digit hour, 24hr format | {d:HH} | 15 |
mm | Minute 00-59 | {d:mm} | 11 |
MM | Month 01-12 | {d:MM} | 02 |
MMM | Month abbreviation | {d:MMM} | Feb |
MMMM | Full month name | {d:MMMM} | February |
ss | Seconds 00-59 | {d:ss} | 29 |
tt | AM or PM | {d:tt} | PM |
yy | Year, 2 digits | {d:yy} | 21 |
yyyy | Year | {d:yyyy} | 2021 |
zz | Timezone offset, 2 digits | {d:zz} | -05 |
zzz | Full timezone offset | {d:zzz} | -05:00 |
: | Separator | {d:hh:mm:ss} | 3:11:29 |
/ | Separator | {d:dd/MM/yyyy} | 26/02/2021 |
^^Date{d:yyyy-MM-dd}^^
to give an output of2021-02-26
Another example for appointment reminders can be made to be more user friendly:Please don't forget your appointment on ^^Date{d:Mx}^^ at ^^Date{d:HH:mm}^^
would yield the followingPlease don't forget your appointment on February 26th at 15:11
A | B | C | D | |
---|---|---|---|---|
1 | name | date | mobile | balance |
2 | Joe | 26/02/2021 15:11:29 | 078 00000000 | 12.50 |
A straight merge is just the name of the spreadsheet column between double-carats ^^. e.g.
^^balance^^
Formatting instructions are added inside curly brackets { }. For number fields the instruction always starts with the letter n followed by a colon : . This is then followed by the actual format instruction. For example:
^^balance{n:c}^^
This tells ExcelSend the field is a number field and the c is the requested output format in the text message.
Straight merge - no formatting:
^^balance^^ = 12.5
With formatting:
^^date{n:c}^^ = $12.50
Here is a table of common specifiers that can be used for a number field:
Specifier | Type | Format | Example Merge Field | Sample Output for 12.5 |
c | Currency | {n:c} | ^^balance{n:c}^^ | $12.50 |
d | Decimal (Works with whole numbers only) | {n:d} | ^^balance{n:d}^^ | 12 |
e | Scientific | {n:e} | ^^balance{n:e}^^ | 1.250000e+001 |
f | Fixed point | {n:f} | ^^balance{n:f}^^ | 12.50 |
g | General | {n:g} | ^^balance{n:g}^^ | 12.5 |
n | Number with commas for thousands | {n:n} | ^^balance{n:n}^^ | 12.50 |
For most of the specifiers in the table above, you can add in a precision value. For example to show a currency value with 3 decimal places you would use
^^balance{n:c3}^^
Specifier | Type | Example | Sample Output for 1500.42 | Note |
0 | Zero placeholder | {n:00.0000} | 1500.4200 | Pads with zeroes. |
# | Digit placeholder | {n:(#).##} | (1500).42 | |
. | Decimal point | {n:0.0} | 1500.4 | |
, | Thousand separator | {n:0,0} | 1,500 | Must be between two zeroes. |
,. | Number scaling | {n:0,.} | 2 | Comma adjacent to Period scales by 1000. |
% | Percent | {n:0%} | 150042% | Multiplies by 100, adds % sign. |
e | Exponent placeholder | {n:00e+0} | 15e+2 | Many exponent formats available. |