Days Between Two Dates (Formula)
How It Works
- What it does: Counts the number of days between the Start and End dates.
- What you get: A whole number – Start 3/1 and End 3/10 shows 9.
- If a value is missing: If Start or End is blank, the cell shows a blank or an error instead of a number.
Common Use Cases
- Length of a project from start to finish
- Gap between order and delivery
- Days a ticket stayed open
- Time between milestones
Details
- Formula Type: Calculated Column
- Formula Category: Dates & Time
Required Columns
| Display name | Column type |
|---|---|
| Start | Date and Time |
| End | Date and Time |
| Days Between Two Dates | Calculated |
Prerequisites
- Create the columns the formula reads: Start and End
- Add or edit a few items so there is data for the formula to work with
How to Apply the Formula
- Add a column, choose More…, then select Calculated (calculation based on other columns)
- Set the data type returned from this formula to Number
- Paste the formula below into the Formula box
- Click OK to create the column
Formula
=DATEDIF([Start],[End],"d")Variations
- Days between two dates:
=DATEDIF([Start],[End],"d") - Days a task took to complete:
=[Date Completed]-[Start Date] - Plain subtraction returns the same number:
=[End]-[Start] - Whole months or years instead: swap
"d"for"m"or"y"in DATEDIF.
Common Questions About Days Between Two Dates Formula
What does this formula do?
Duration in days. It lives in a calculated column, so SharePoint fills the value in automatically for every item.
Which columns do I need to set this up?
You need: Start (Date), End (Date). The formula reads these to produce its result.
Can I change the formula for my own columns?
Yes. Swap the column names in brackets for your own, and adjust any numbers or text.
If LookBook 365 is code-free and out-of-the-box, why does this example use a formula?
A SharePoint formula is native, declarative configuration, not custom development. It only works with the item’s own fields and simple logic – it cannot run scripts, reach external services, or change other data, and nothing is deployed to your tenant. It keeps working through Microsoft updates. If you would rather not set this up yourself, this is exactly the kind of thing I configure for clients.
How do I calculate how many days a task took to complete?
Use =[Date Completed]-[Start Date], or =DATEDIF([Start Date],[Date Completed],"d") for the same answer. Set the column to return a Number so the result shows as a count of days.
Why does my result show as a date instead of a number?
The calculated column is still set to return Date and Time. Edit the column and set the data type returned from this formula to Number.
