SPREAD
SPREAD(amount, from, to, [shape])
Spreads 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. It spreads a number, never a duration, so say how to count a duration first: SPREAD(INHOURS([Duration]), …) or INDAYS. In the grid the cell shows the whole amount; charts, CLIP, AT and TOTAL read how it falls over time. See Amounts and levels.
Arguments
| Argument | What to give it |
|---|---|
| amount | A number: hours, cost, quantity. A duration is refused; count it with INHOURS or INDAYS. |
| from, to | Dates. The amount accrues over the working time from exactly from to exactly to, to the second. If to is at or before from, or there is no working time between them, the whole amount lands at from in one step. |
| shape | Optional. Evenly over working time when left out. Otherwise a CURVE (front-loaded, back-loaded, a bell), another amount over time (in proportion to its part inside the window), or a level over time (a 0/1 level keeps the work out of some stretches). |
A blank amount, date or shape gives a blank row (a blank shape isn't read as even: write COALESCE([Curve], "even") for that).
Examples
Formula=SPREAD(INHOURS([Duration]), [Start date], [Finish date])
The task's hours, spread evenly over its working days. Added up per week, it is the task's share of a histogram.
Formula=SPREAD(INHOURS([Remaining duration]), MAX(DATADATE(), [Start date]), [Finish date])
The work still to do, from the data date (or the task's start, if later) to its finish: the forecast part of an S-curve.
Formula=SPREAD(INHOURS([Duration]), [Start date], [Finish date], CURVE([Start date], [Finish date], "back"))
The same hours, back-loaded: little at the start, most near the finish.
Formula on assignments=SPREAD([Budgeted units] * [Hourly rate], Task[Start date], Task[Finish date])
On an assignment: its planned cost, spread over its task's working time. Added up on the project with SUM, it is the cost-loaded cash flow.
Reach for something else when
| You want | Use |
|---|---|
| A total to date that you already have at each update, as an amount | CHANGE of HISTORY |
| The part of the amount in a window | CLIP |
| A number back: the running total on a date, or the whole | AT, TOTAL |
| Amounts of many tasks added into one | SUM: SUM(SPREAD(INHOURS(Tasks[Duration]), Tasks[Start date], Tasks[Finish date])) |
Common questions
Why can't I spread [Duration] directly?
A duration is working time, and a day of it means a day of the task's own calendar, so the spread wouldn't know whether you want days or hours. The editor says: "SPREAD spreads a number. For a duration, say how to count it: INDAYS(…) or INHOURS(…)".
The editor refuses this=SPREAD([Duration], [Start date], [Finish date])
Why can't I take the MAX of a spread for the peak?
An amount builds up; it has no peak of its own. The editor says: "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 take the MAX of each week's TOTAL.
Can I compare a spread with a number?
No: "Values over time can't be compared. Read one as a number first, with TOTAL(…) or AT(…, date)".
Does it round to whole days?
No. Values over time are exact to the second, so a task that starts at 08:00 on Monday on an 8-to-5 calendar has 4 of its hours by noon, and an in-progress task's remaining work starts at the data date itself.