Skip to content

Formulas and functions

A formula is an expression in braces that JungleMail 365 works out when it sends a newsletter, separately for each recipient. With formulas you can insert a date, change how a recipient’s details look, or show different words to different people: {FormatDate(Today; "MMMM yyyy")} becomes the month and year the newsletter is sent in, such as March 2027.

The newsletter wizard can write the formula for you. In the Recipients step:

The Placeholders & Formulas dialog, numbered: 1 the lightning bolt, 2 the merge tags, 3 Insert as, 4 the formula, 5 Insert

  1. Select the lightning bolt at the right of Subject or Preview text (shown after the subject in the inbox), then Placeholders & Formulas.
  2. On Recipient placeholders, select a merge tag.
  3. In Insert as, choose what to do with it. The choice depends on the merge tag: formats for a date, rounding for a number, capitals or a shorter version for text.
  4. Check the formula below the list.
  5. Select Insert.

For the date and time the newsletter is sent, use the Formulas tab instead: each line shows today’s date in its format. In the email builder, Insert merge tag offers the same choices under Format, and one more for a date, Convert date to another time zone. You can also type a formula anywhere in the subject, the preview text or the text of the email.

Choice For Inserts
Column value Any The merge tag alone: {First name}
Capitalize the first letter in each word Text {Proper(First name)}
Retrieves a first word from text Text {FirstWord(First name)}
Retrieves a substring from text Text {SubString(First name; 50)}
Crop text to approximate characters length without word cut-off Text {LimitText(First name; 50)}
Converts text to lowercase Text {Lower(First name)}
Converts text to uppercase Text {Upper(First name)}
Converts Rich text to Plain text Rich text {PlainText([Summary])}
Format date – extract year Date {FormatDate([Start Date]; "yyyy")}
Format date – extract month Date {FormatDate([Start Date]; "MMMM")}
Format date – extract day Date {FormatDate([Start Date]; "dd")}
Full date Date {FormatDate([Start Date]; "dddd, dd MMMM yyyy")}
Format date – e.g.: February 20, 2017 Date {FormatDate([Start Date]; "MMMM dd, yyyy")}
Format date – 12-hour format Date {FormatDate([Start Date]; "MM/dd/yyyy h:mm tt")}
Format date – 24-hour format Date {FormatDate([Start Date]; "MM/dd/yyyy hh:mm")}
Convert date to another time zone Date, in the email builder {ConvertTimeZone([Start Date]; "Eastern Standard Time")}
Converts number to a currency text representation Number {CurrencyToLiteral([Amount])}
Will round a number to a specified number of digits Number {Round([Amount]; 2)}
  • Braces mark a formula. Text outside them stays as you typed it.
  • Arguments are separated by semicolons: {SubString("EnovaPoint"; 0; 5)}. Commas work too, unless your site’s regional format writes decimals with a comma.
  • Text goes in quotes: "MMMM". Straight and curly double quotes both work.
  • Names with characters such as - or : go in square brackets: {[E-mail]}. The columns of a SharePoint list are inserted that way anyway: {[Start Date]}.
  • Function names are not case-sensitive: upper works like Upper.
  • Dates and numbers follow your site’s regional format, and Now and Today its time zone (Language, time zone and formats).
  • A formula JungleMail cannot work out stays in the email as you typed it, braces included: for example a misspelled name, or a missing argument. Check the newsletter in the Preview step before you send it.
  • In the HTML Editor, formulas inside an element’s style attribute are left as they are.
Value Gives
Now The date and time the email is sent
Today The date the email is sent
True, False Yes and no, for example to compare with in If
Null Nothing: an empty value
"text", 42 The text or number as you typed it
A merge tag That detail of the recipient (Merge tags)
Operator Does Example
+, - Adds and subtracts. With a date, adds or subtracts days; one date minus another gives the number of days between them {Today + 7}
*, / Multiplies and divides {Round([Amount] / 12; 2)}
& Joins text {"Hi " & First name}
=, <>, <, >, <=, >= Compares two values. The result is True or False {If(Office = "London"; "See you in the atrium"; "Join online")}
: Takes one part of a value that has several, such as a column of a SharePoint content item {S:[Title]}
Function Gives Example
Upper(text) The text in capital letters {Upper("London")} gives LONDON
Lower(text) The text in lowercase {Lower("EnovaPoint")} gives enovapoint
Proper(text) Each word with a capital first letter and the rest in lowercase {Proper("priya RAMAN")} gives Priya Raman
FirstWord(text) The first word {FirstWord("Priya Raman")} gives Priya
LastWord(text) The last word {LastWord("Priya Raman")} gives Raman
SubString(text; start) The text from position start on. The first character is at position 0 {SubString("EnovaPoint"; 5)} gives Point
SubString(text; start; length) length characters from position start {SubString("EnovaPoint"; 0; 5)} gives Enova
LimitText(text; length) The text cut to about length characters without breaking a word. Cut text ends with ..., unless it ends with ., ? or ! {LimitText([Summary]; 120)}
PadLeft(text; length), PadLeft(text; length; character) The text filled on the left up to length characters, with spaces or the character you give {PadLeft("7"; 3; "0")} gives 007
PadRight(text; length), PadRight(text; length; character) The same, filled on the right {PadRight("7"; 3; "0")} gives 700
Replace(text; search; replacement) The text with every search replaced, whatever its case {Replace(Department; "&"; "and")}
Contains(text; search) True if the text contains search, whatever its case {If(Contains(Job title; "Manager"); "…"; "…")}
IfContains(values; value; ifFound; ifNotFound) ifFound if value is one of the comma- or semicolon-separated values, such as a choice column’s {IfContains([Topics]; "Events"; "See the events below"; "")}
IsEmpty(value) True if the value is empty {If(IsEmpty(Office); "…"; "…")}
IfEmpty(value; fallback) The value, or fallback when it is empty {IfEmpty(First name; "colleague")}
IfEmpty(value; ifEmpty; ifNotEmpty) ifEmpty when the value is empty, otherwise ifNotEmpty
PlainText(value) Rich text without its formatting {PlainText([Summary])}
Function Gives Example
Now(), Today() The same as the values Now and Today
FormatDate(date; format) The date written in a format, with the codes below {FormatDate(Today; "dddd, MMMM d")}
ConvertTimeZone(date; timeZone) The date and time in another time zone, named as Windows or IANA names it: "Arabian Standard Time" or "Asia/Dubai". A date without a time is not changed {FormatDate(ConvertTimeZone(Now; "Asia/Dubai"); "HH:mm")}
WeekOfYear(), WeekOfYear(date), WeekOfYear(date; True) The week number of today or of the date, by your regional format’s rules; with True, the ISO 8601 week {WeekOfYear(Today; True)}
IsWholeMonth(start; end) True if the two dates are the first and the last day of the same month

The format codes FormatDate uses, shown for Tuesday, March 16, 2027, at 9:05 in the morning:

Code Gives Example
d The day of the month 16
dd The day, always two digits 16
ddd The short day name Tue
dddd The day name Tuesday
M The month number 3
MM The month, always two digits 03
MMM The short month name Mar
MMMM The month name March
yy The year, two digits 27
yyyy The year 2027
H The hour, 0 to 23 9
HH The hour, 00 to 23 09
h The hour, 1 to 12 9
hh The hour, 01 to 12 09
mm The minutes 05
ss The seconds 00
tt AM or PM AM

Day and month names are in the language of your regional format. A format of one letter on its own is a standard format: "d" is the short date and "t" the short time. In a format, / and : become your regional format’s date and time separators; to keep a character as typed, put it in single quotes: "MM'/'dd'/'yyyy". Microsoft lists every code in Custom date and time format strings.

Function Gives Example
Round(number), Round(number; digits) The number rounded to whole numbers or to digits decimals, halves away from zero. Negative digits round to tens, hundreds and so on {Round(2412; -2)} gives 2400
Abs(number) The number without its sign {Abs(-5)} gives 5
CurrencyToLiteral(amount), CurrencyToLiteral(amount; culture) The amount in English words. The currency comes from the SharePoint currency column, or from the culture you name. Dollars and euros are written out; other currencies keep their symbol {CurrencyToLiteral(2500; "en-US")} gives two thousand five hundred dollars and zero cents
Function Gives
If(condition; ifTrue), If(condition; ifTrue; ifFalse) ifTrue when the condition is true, otherwise ifFalse, or nothing
If(condition1; value1; condition2; value2; …; otherwise) The value of the first condition that is true, otherwise the last value
And(condition; condition; …) True if every condition is true
Or(condition; condition; …) True if at least one condition is true
Not(condition) The opposite

For example, {If(Office = "London"; "See you in the atrium"; Office = "Toronto"; "See you on the third floor"; "Join us online")} gives each office its own sentence.

Function Gives Example
Hyperlink(url; text) In a Word report, a link with that text. In an email, only the address, as plain text {Hyperlink("https://enovapoint.sharepoint.com/sites/intranet"; "Open the intranet")}
Html(html), Html(html; text) The HTML inserted as HTML rather than as text, with text as its plain-text version. JungleMail checks and cleans the HTML first

These work on a value that holds several items, such as a SharePoint lookup column that allows several values.

Function Gives
JoinValues(list; "Column"), JoinValues(list; "Column"; separator) That column of every item, separated by ; or by the separator you give
JoinUniqueValues(list; "Column") The same, each value once
Count(list) The number of items
Sum(list; "Column"), Average(list; "Column") The total and the average of that column
Min(list; "Column"), Max(list; "Column"), Median(list; "Column") The smallest, the largest and the middle value of that column

In a section or block that shows SharePoint content, a formula can use the columns of the item being shown: S: for the section’s item and B: for the block’s, as in {S:[Title]} (Merge tags). Two functions are there only:

Function Gives
ReadingTime() How long a SharePoint news page takes to read, at 200 words a minute: “1 minute”, “4 minutes”
CanvasToHtml(page) The content of a modern SharePoint page, as HTML

With DeepL turned on (Turn AI features on or off), Translate(text) translates the text into the language of the recipient’s version of the newsletter, and Translate(text; "fr") into the language whose code you give. If a translation fails, the email shows the reason in square brackets instead.

  • A detail some recipients lack: {IfEmpty(Office; "your office")}. It is shorter than If(IsEmpty(…); …), and nobody gets a sentence with a gap in it.
  • A summary that has to fit: {LimitText([Summary]; 120)}. Unlike SubString, it doesn’t cut a word in half, and it shows with ... that there is more.
  • Names typed in capitals, in a file or a list: {Proper(First name)} turns PRIYA into Priya.
  • A date read in several countries: write the month as a word, {FormatDate([Start Date]; "MMMM d, yyyy")}. 03/04/2027 is March 4 to EnovaPoint’s Chicago office and April 3 to its London office.
  • A 24-hour time: type HH, as in {FormatDate([Start Date]; "HH:mm")}. hh is the 12-hour clock, and without tt nobody can tell morning from evening.
  • A date in a recurring newsletter: build it from Today, such as {FormatDate(Today + 7; "dddd, MMMM d")} for a deadline a week after each issue, so that every issue shows its own date (Set up a recurring newsletter).