Values over time
Some values aren't one number but a number that changes over time: a task's hours spread over the weeks it works, its % complete at each update. A formula can build one, and a chart draws it week by week or month by month. In the grid, a cell shows it as one number: an amount's total, or a level's latest value.
Values over time are exact to the second and follow each task's own calendar. A task working Monday 08:00 to Friday 17:00 on an 8-to-5 calendar has 4 of its hours by Monday noon, and none on the weekend. Nothing is rounded to days, so a week's total in a chart is the exact part of each task's work that falls in it.
Amounts and levels
There are two kinds, and which one you have decides what you can do with it.
| An amount | A level | |
|---|---|---|
| Is | Something that adds up over time: hours, cost, work | Something that holds from one moment to the next: % complete at each update, a rate, a 0/1 mask |
| Made by | SPREAD, CHANGE | HISTORY, or one amount divided by another |
| Read back as a number | TOTAL (the whole), AT (the running total to a date) | AT (its value on a date), MAX, MIN, AVERAGE |
| Shown in a cell | Its total | Its latest value |
HISTORY of a date field gives a third kind, a date over time: the finish as it was forecast at each update. Read it with AT.
Spread work over time
Formula column Planned hoursPlanned hours = SPREAD(INHOURS([Duration]), [Start date], [Finish date])
The task's hours, spread evenly over its working time. A duration is never spread as it is: say whether you count it in days or hours with INDAYS or INHOURS.
A CURVE makes the spread uneven:
Formula=SPREAD(INHOURS([Duration]), [Start date], [Finish date], CURVE([Start date], [Finish date], "back"))
The same hours, loaded towards the finish. The shapes are "even", "front", "back" and "bell", or your own running percentages at equal steps.
To keep only part of it, CLIP cuts out a window:
Formula=TOTAL(CLIP(SPREAD(INHOURS([Duration]), [Start date], [Finish date]), [Start date], DATADATE()))
The hours that fall before the data date.
Amounts add up with + and SUM, even across tasks on different calendars:
=SUM(Tasks[Planned hours])
On a WBS row: the Planned hours of every task under the node, added up over time.
| Expression | Gives |
|---|---|
amount ± amount, SUM of amounts | An amount |
| amount × number, amount × level | An amount: scaled, or masked by a 0/1 level |
| amount ÷ amount | A level: their ratio, e.g. earned over planned |
level with level or number (+ - * /) | A level |
amount × amount, amount + number, a comparison, & | Refused. Read it as a number first, with TOTAL or AT |
History across updates
HISTORY is a field's value as of each update of the schedule: each update holds from its data date until the next.
Formula=AT(HISTORY([% complete]), DATADATE())Formula
=MAX(HISTORY([% complete]))
The highest % complete the task has reported at any update.
Formula columns have history too: HISTORY([Cost]) is your Cost column worked out on each past update, from that update's own data. CHANGE turns a running total, like % complete, into what was added between updates, as an amount.
Periods
PERIODS makes the days, weeks, months, quarters or years between two dates, as rows you can count, filter and read:
Formula=COUNT(PERIODS("week", [Start date], [Finish date]))
How many calendar weeks the task touches.
Each period has a [Start date] and a [Finish date], and the Tasks, Assignments and Relationships working in it. Read a value over time in each period with CLIP and the periods' dates:
=LET(m, PERIODS("month", Project[Start date], Project[Finish date]), MAX(TOTAL(CLIP(SUM(Tasks[Planned hours]), m[Start date], m[Finish date]))))
A measure: the planned hours of the busiest month.
Periods start at midnight on the project's clock, weeks on the project's week start, quarters in January, April, July and October.
Common questions
Why is MAX of my hours refused?
An amount has no peak: it only adds up. "An amount over time has no peak: add it up with TOTAL(…), or divide it by another amount for a rate (a level over time) and take its MAX". For the busiest week, cut it into weeks with PERIODS and CLIP, then take the MAX of their totals.
Why is HISTORY([Remaining duration]) refused?
A duration over time must say how it is counted: HISTORY(INDAYS([Remaining duration])) or HISTORY(INHOURS([Remaining duration])).
Can I compare a value over time with a number?
Not directly: read it as a number first. TOTAL(…) > 100 compares its total, AT(…, DATADATE()) > 50 its value at the data date.