Syntax
PRORATE(number, start_date, end_date, prorate_start, prorate_end, [decimal_places])
- number—The number to prorate
- start_date—The first date in the value range
- end_date—The last date in the value range
- prorate_start—The first date in the range to prorate
- prorate_end—The last date in the range to prorate
- decimal_places—[optional][Optional] The default is 2. The number of decimal places to round the result
Sample usage
PRORATE(100, [Due Date]1, [Due Date]4, [Due Date]3, [Due Date]4)
Usage notes
Imagine you have a total cost, revenue, or benefit for an entire year, but you only need to determine the amount applicable to a few specific months or days. PRORATE helps you accurately divide that value to match the proportion of the dates you're interested in. It's especially useful for allocating expenses, income, or resources when dealing with partial periods.
Examples
Example 1: Prorating a yearly subscription cost
Scenario: You have an annual software subscription that costs $1,200 per user (for 365 days, from January 1, 2025, to December 31, 2025). A new team member joins on July 1, 2025, and you want to calculate their prorated cost for the remainder of the year (July 1, 2025, to December 31, 2025).
To set this up in Smartsheet, you might have columns like this:
| Column name | Row 1 (example) |
|---|---|
| Total Annual Cost | 1200 |
| Original Start Date | 01/01/2025 |
| Original End Date | 12/31/2025 |
| Prorate Start Date | 07/01/2025 |
| Prorate End Date | 12/31/2025 |
| Prorated Cost | $601.64 See explanation below |
Example formula in the Prorated Cost row:
=PRORATE([Total Annual Cost]@row, [Original Start Date]@row, [Original End Date]@row, [Prorate Start Date]@row, [Prorate End Date]@row)
Output: Approximately $601.64 (This represents the cost for 184 days out of 365 days, which is roughly half a year's cost).
Example 2: Prorating a project budget for a shorter duration
Scenario: A project has a total budget of $50,000 for a duration from January 1, 2025, to June 30, 2025. Due to a scope change, the project will now only run from March 1, 2025, to April 30, 2025. You want to determine the prorated budget for this new, shorter duration.
| Column name | Row 1 (example) |
|---|---|
| Total Project Budget | 50000 |
| Original Project Start | 01/01/2025 |
| Original Project End | 06/30/2025 |
| New Project Start | 03/01/2025 |
| New Project End | 04/30/2025 |
| Prorated Budget | $16,393.44 See explanation below |
Example formula in the Prorated Budget row:
=PRORATE([Total Project Budget]@row, [Original Project Start]@row, [Original Project End]@row, [New Project Start]@row, [New Project End]@row)
Output: Approximately $16,393.44 (representing the budget for 61 days out of the original 181 days).
Still need help?
Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.
FFind examples of how other Smartsheet customers use this function, or ask about your pecific use case in the Smartsheet online Community.