PERIODS
PERIODS("day" | "week" | "month" | "quarter" | "year", from, to, [time zone])
The 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. Each period has a [Start date] and a [Finish date], so a value over time can be cut into them with CLIP or read at each with AT. Periods start at midnight in the project's time zone, and weeks on the project's week start (Monday unless the scheduling settings say otherwise).
Arguments
| Argument | What to give it |
|---|---|
| unit | "day", "week", "month", "quarter" or "year", written out. Quarters start in January, April, July and October. |
| from, to | Single dates. Every period from the one holding from to the one holding to is included; none if to is before from. For many rows' dates, pick one: MIN(Tasks[Start date]). |
| time zone | Optional. Another zone's midnights, by its time zone database name: "Europe/London", "America/Chicago". |
Each period's [Finish date] is the moment the next one starts. A period also reaches the Tasks working in it, their Assignments and the Relationships into them, which is how charts break work down by week or month. More in Periods.
Examples
Formula=COUNT(PERIODS("week", [Start date], [Finish date]))
How many calendar weeks the task touches.
Measure (on the project)=LET(m, PERIODS("month", [Start date], [Finish date]), MAX(TOTAL(CLIP(SUM(SPREAD(INHOURS(Tasks[Duration]), Tasks[Start date], Tasks[Finish date])), m[Start date], m[Finish date]))))
As a measure: the hours in the busiest month of the project.
Measure (on the project)=LET(w, PERIODS("week", [Start date], [Finish date]), COUNT(FILTER(w, TOTAL(CLIP(SUM(SPREAD(INHOURS(Tasks[Duration]), Tasks[Start date], Tasks[Finish date])), w[Start date], w[Finish date])) > 40)))
How many weeks plan more than 40 hours of work: a quick check on overloaded weeks.
Formula=COUNT(PERIODS("day", [Start date], [Finish date], "Europe/London"))
How many London calendar days the task touches, for a team working on UK time.
Reach for something else when
| You want | Use |
|---|---|
| Working time between two dates | WORKDAYS |
| Calendar days between two dates | CALDAYS |
| The week number of one date | WEEKNUM |
Common questions
Is there a fortnight or a custom period?
No. The editor says: “There's no period fortnight: PERIODS counts in "day", "week", "month", "quarter" or "year"”.
=COUNT(PERIODS("fortnight", [Start date], [Finish date]))
Why is a list of dates refused?
PERIODS spans from one date to another: "PERIODS takes one date for from, not a list: the first or last of them, e.g. MIN(Tasks[Start date])".
How many periods can I read?
At most 100,000 at once. Days over several centuries go past that; count in a longer unit, such as weeks.
Is a week the same row in every formula?
Yes. Periods are counted from a fixed date, not stored, so the same week is the same period wherever you read it, and two formulas over weeks line up.