Function reference

Every function a formula can use, by what it does. Search by a name, or by what you want to do ("float", "working days", "baseline"). Each page has the function's arguments, worked examples, and the functions people confuse it with.

In the formula editor, the ⓘ beside a function's argument hint, or beside it in the suggestions, opens its page here.

Aggregates

ANYTRUE when at least one value in a list of TRUE/FALSE is TRUE, such as whether any predecessor is critical.ANY(list)AVERAGEGives the mean of a list of numbers, durations or dates, such as the average duration of the tasks in a phase.AVERAGE(list)COUNTCounts the rows in a set, such as a task's successors, or the values in a list that aren't blank.COUNT(set_or_list)COUNTDISTINCTCounts how many different values a column holds across a set of rows, such as how many phases feed into a task.COUNTDISTINCT(list)EVERYTRUE when every value in a list of TRUE/FALSE is TRUE, such as whether all of a task's predecessors are complete.EVERY(list)MAXGives the largest value in a list, such as the latest finish among a task's predecessors, or the larger of two values.MAX(list)MEDIANGives the middle value of a list of numbers, durations or dates, such as the typical duration of the tasks in a phase.MEDIAN(list)MINGives the smallest value in a list, such as the earliest start among a task's successors, or the smaller of two values.MIN(list)PERCENTILEGives the value below which a share `p` of a list falls, such as the float that the tightest tenth of the schedule is under.PERCENTILE(list, p)PERCENTRANKGives the share of a list's values that are below this one, from 0 to 1, such as how a task's duration compares with every other task's.PERCENTRANK(value, list)RANKGives a value's place in a list, such as where a task's float stands among every task's.RANK(value, list, [ascending])SUMAdds up a list of numbers or durations, such as the durations of a task's predecessors.SUM(list)TEXTJOINJoins the values of a column into one piece of text, such as the codes of a task's predecessors separated by commas.TEXTJOIN(separator, list)

Sets

ARGMAXThe row of a set with the largest value, such as the upstream task that finishes last, so you can read any of its fields.ARGMAX(set, list)ARGMINThe row of a set with the smallest value, such as the successor with the least float, so you can read any of its fields.ARGMIN(set, list)EXCEPTKeeps the rows of the first set that aren't in the second, such as everything upstream of a task except its direct predecessors.EXCEPT(set, set)FILTERKeeps the rows of a set where a condition is TRUE, such as the predecessors that aren't complete yet.FILTER(set, condition)FIRSTOne row of a set: the first in schedule order, or blank when the set is empty.FIRST(set)INCLUDESTRUE if a given row is in a set, such as whether a task lies upstream of the substantial-completion milestone.INCLUDES(set, task)INTERSECTKeeps only the rows that are in both sets, such as the tasks upstream of this one that are also in its phase.INTERSECT(set, set)ISEMPTYTRUE if a set has no rows, such as a task with no successors (an open end).ISEMPTY(set)ONLYThe single row of a set that should hold exactly one, such as the one predecessor of a task you expect to have one.ONLY(set)ROWSTurns a column of rows into a set of those rows, such as every task that some relationship leads into.ROWS(list)SAMEEvery row of the table whose field has the same value as this row's, this row included: the tasks in this task's phase, or in its WBS node.SAME([field])TASKOne task, named by its ID, so you can read its fields from any row: `TASK("FINISH")[Finish date]`.TASK("code")UNIONCombines two sets into one, holding every row that is in either, such as a task's predecessors and its successors together.UNION(set, set)

Logic

ANDTRUE when every condition is TRUE, such as a task that is both critical and long.AND(a, b, …)BLANKA blank value, for a row that should have no value at all, such as a finish date shown only on critical tasks.BLANK()COALESCEGives the first of its values that isn't blank, such as the % complete or 0 when none is entered.COALESCE(a, b, …)IFChooses one of two values by a TRUE/FALSE condition, such as labelling critical tasks.IF(condition, then, [else])IFERRORGives the value, or the fallback on rows where working out the value failed, such as a division by zero on a milestone.IFERROR(value, fallback)IFSGives the value paired with the first condition that is TRUE, for a ladder such as critical, near-critical, OK.IFS(condition, value, …)INPUTReads one of the project's inputs: a named value for the whole project, such as how long a slip to test, that every row reads the same.INPUT("name")ISBLANKTRUE when a value is blank: a field nobody has filled in, such as the actual finish of a task that hasn't finished, or the MAX of a list with nothing in it.ISBLANK(value)ISERRORTRUE when working out a value failed on that row: a division by zero, ONLY on a set that doesn't hold exactly one row, the square root of a negative number.ISERROR(value)LETNames values so the result can use them, such as naming a task's slip once and then banding it.LET(name, value, …, result)NOTTurns TRUE into FALSE and FALSE into TRUE, such as "has successors" from "has none".NOT(a)ORTRUE when any condition is TRUE, such as a task that is critical or close to it.OR(a, b, …)SWITCHCompares one value with a list of matches and gives the result paired with the one it equals, such as a weight for each status.SWITCH(value, match, result, …, [default])

Math

ABSThe value without its sign, such as how far a task has moved from its baseline in either direction.ABS(number)CLAMPKeeps a value between a low and a high limit, such as a ratio held between 0 and 1.CLAMP(value, low, high)MODThe remainder after dividing one number by another, such as the days a duration runs past its last whole week.MOD(number, divisor)ROUNDRounds a number to a number of decimal places, such as a % complete to a whole percent.ROUND(number, [digits])ROUNDDOWNRounds a number towards zero, such as the whole weeks of float a task has.ROUNDDOWN(number, [digits])ROUNDUPRounds a number away from zero, such as the whole weeks a task still needs.ROUNDUP(number, [digits])SQRTThe square root of a number, such as a step in a standard deviation of task durations.SQRT(number)

Text

CONTAINSTRUE when a piece of text has another inside it, such as a task name that mentions "pour".CONTAINS(text, part)LEFTGives the first few characters of a piece of text, such as the area prefix at the start of a task code.LEFT(text, count)LENCounts the characters in a piece of text, such as a task name, to find names that are too long or codes that don't follow the pattern.LEN(text)LOWERGives the text in small letters, for building labels or keys that look the same on every row.LOWER(text)MIDGives characters from the middle of a piece of text, such as the level or system digits inside a task code.MID(text, start, count)RIGHTGives the last few characters of a piece of text, such as the sequence number at the end of a task code.RIGHT(text, count)STARTSWITHTRUE when a piece of text begins with another, such as task codes that start with an area prefix.STARTSWITH(text, part)SUBSTITUTEReplaces every occurrence of one piece of text with another, such as the separators in a WBS path.SUBSTITUTE(text, old, new)TEXTTurns a value into text, as the grid shows it or in a format you write with Excel's codes, such as a finish date as "Fri 9 Jan" in a label.TEXT(value, [format])TEXTAFTERThe part of a piece of text after a delimiter, such as the structure key at the end of a task code like FAB-G30-PR-001, or the lowest level of a WBS path.TEXTAFTER(text, delimiter, [instance])TEXTBEFOREThe part of a piece of text before a delimiter, such as the zone at the start of "Zone 01A - Rebar" or the prefix of a task code.TEXTBEFORE(text, delimiter, [instance])TRIMRemoves spaces from both ends of a piece of text, the kind an import or a pasted name leaves behind.TRIM(text)UPPERGives the text in capitals, for showing codes or labels the same way on every row.UPPER(text)VALUEThe number a piece of text is, such as a crew count kept in a text attribute ("07") or a floor number cut out of a name.VALUE(text)

Dates

ADDWORKThe date that a given amount of working time after another date lands on, stepping through the calendar the way CPM does.ADDWORK(date, duration, [calendar])CALDAYSThe number of calendar days between two dates, weekends and holidays included, such as how many days are left until a milestone.CALDAYS(from, to)DATADATEThe schedule's data date: the date progress is reported up to and the date CPM schedules remaining work from.DATADATE()DATEMakes a date from a year, a month and a day, such as a contract milestone to compare finishes against.DATE(year, month, day, [hour], [minute])DAYThe day of the month of a date, from 1 to 31, such as the day a task finishes on.DAY(date)EOMONTHThe last day of a month, some months before or after a date, such as the end of the month a task finishes in for monthly reporting.EOMONTH(date, months)HOURThe hour of the day of a date, from 0 to 23, such as for checking that work happens between 08:00 and 17:00.HOUR(date)INDAYSTurns a duration into a plain number of working days, such as a task's float as a number to round, chart or put in text.INDAYS(duration)INHOURSTurns a duration into a plain number of hours, such as a task's duration for costing or spreading over time.INHOURS(duration)MINUTEThe minutes past the hour of a date, from 0 to 59, read in UTC wall clock as every date is.MINUTE(date)MONTHThe month of a date as a number from 1 (January) to 12 (December), such as the month a task finishes in.MONTH(date)TODAYToday's date, for questions about the real calendar such as how many days until a task starts.TODAY()WEEKDAYThe day of the week of a date, from 1 (Sunday) to 7 (Saturday), such as for finding tasks that start on a weekend.WEEKDAY(date)WEEKNUMThe week of the year a date falls in, counting the week that holds 1 January as week 1 and starting each week on a Sunday, as Excel's WEEKNUM does by default.WEEKNUM(date)WORKDAYSThe working time between two dates, such as how much work time is left from the data date to a task's finish.WORKDAYS(from, to, [calendar])YEARThe year of a date as a number, such as the year a task finishes in, for grouping or comparing by year.YEAR(date)

What-ifs and other schedules

BASELINEThe project's baseline, as a schedule you read with [IN](/docs/formulas/functions/in): `IN(BASELINE(), [Finish date])` is each task's baseline finish.BASELINE()FLOATPATHWhich float path into the task you name each task is on.FLOATPATH(task)FLOATTOHow far each task can slip before the task you name moves.FLOATTO(task)INWorks out an expression in another schedule: a what-if, the baseline, or a named revision or scenario, for the same row.IN(what-if, value)SCHEDULEAnother stored schedule of the project, by its name: a named revision or a scenario, read with [IN](/docs/formulas/functions/in) exactly as the baseline is.SCHEDULE("name")SETTINGNames a project setting to change in a what-if, such as the data date or a must-finish-by date, and is followed by its new value.SETTING("name")WHATIFAsks CPM to date the schedule again with some inputs changed: a task takes longer, moves to another calendar, or the data date moves.WHATIF(tasks, field, value, …)

Over time

ATReads a value over time on one date, as a plain value: for an amount, its running total up to that moment; for a level, its value then; for a date over time, the date as it was forecast then.AT(value over time, date)CHANGETurns a running total, recorded at each update, into what was added between updates: an amount over time.CHANGE(level over time, [from], [shape])CLIPCuts out the part of a value over time between two dates: the hours that fall this month, or the cost still ahead of the data date.CLIP(value over time, from, to)CURVEA shape for [SPREAD](/docs/formulas/functions/spread): how the work builds up between two dates, from none at the start to all of it at the end.CURVE(from, to, shape)HISTORYA field's value as of each update of the schedule, from the first saved update to today: a [level](/docs/formulas/over-time#amounts-and-levels) over time, which holds each update's value from that update's data date until the next.HISTORY([field])PERIODSThe days, weeks, months, quarters or years from the one holding `from` to the one holding `to`, as a set of period rows you can count, filter and read like tasks.PERIODS("day" | "week" | "month" | "quarter" | "year", from, to, [time zone])SPREADSpreads a number over the working time between two dates, on the row's calendar, so it becomes an amount over time: hours or cost that build up day by day as the task works.SPREAD(amount, from, to, [shape])TOTALThe whole of an amount over time, as a plain number: all the hours a spread holds, or how much falls in a window when the amount has been [CLIP](/docs/formulas/functions/clip)ped.TOTAL(amount over time)

For how the pieces fit together (fields, durations, blanks, other rows), start with How a formula reads.