How to create a column for time which will take the difference in the Start and the End time and capture the difference

Question

How to create a column in SharePoint for time which will take the difference in the Start and the End time and capture the difference

Answer 1

Hi @Subrata S Roy,
Thank you for posting your question to Microsoft Q&A Forum.

In SharePoint, this can be done using a Calculated column, because SharePoint stores date/time values as numbers (where the difference is calculated in days).

Step 1: Create the Start and End columns

Make sure both columns are created as:

• Column type: Date and Time
• Format: Date & Time or Time only (either works)

Step 2: Create a Calculated column

1. Click Add column > More
2. Select Calculated (calculation based on other columns)
3. Enter a column name, e.g. Duration

Step 3: Use the appropriate formula

• Duration in hours

=([End Time] - [Start Time]) * 24

Then set the column type to Number with 1 decimal.

This works because SharePoint returns the difference in days, so multiplying by 24 converts it to hours

• Duration in minutes

=([End Time] - [Start Time]) * 1440

(24 × 60 = 1440 minutes per day)

• Duration as Days, Hours, Minutes (text format)

=CONCATENATE(INT([End date ]-[Start date ]),"Days",TEXT([End date ]-[Start date ],"h""hrs""m""mins"""))

1. Return type: Single line of text
2. Useful for display purposes only (not for calculations)

However, there are a few important limitations to note:

• Calculated columns do not support dynamic functions such as NOW() for real‑time calculations.
• If the End Time occurs before the Start Time (for example, overnight scenarios), the calculated result may be negative.
• For advanced use cases such as overnight durations, break handling, or real‑time tracking, Power Automate or Power Apps should be used instead.

I hope this information is helpful. Please follow the steps above and let me know if it works for you. If not, we can continue working together to resolve the issue.  

If you have any questions or need additional assistance, please don’t hesitate to reach out so I can continue to support you.

Thank you for your patience and understanding. If you found the response useful, please consider marking it as accepted, as this may help other community members who are looking for similar guidance.  

I look forward to continuing the conversation.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Answer 2

To create a column in SharePoint that calculates the difference between a Start time and an End time, you can use calculated columns. Here’s how you can do it:

1. Create the Start and End Time Columns: First, ensure you have two columns in your SharePoint list: one for the Start time and another for the End time. These should be set as Date and Time type columns.
2. Add a Calculated Column: Create a new column in your SharePoint list and set its type to "Calculated".
3. Define the Formula: In the formula field for the calculated column, you can use a formula similar to the following to calculate the difference in hours:

      =([End Time] - [Start Time]) * 24
   

   This formula subtracts the Start time from the End time and multiplies the result by 24 to convert the difference from days to hours. If you want the difference in minutes, you can multiply by 1440 instead (24 hours * 60 minutes).
4. Set the Data Type: Make sure to set the data type returned from the formula to "Number" or "Single line of text" depending on how you want to display the result.
5. Save the Column: Once you have entered the formula and set the data type, save the column. The calculated column will now display the difference between the Start and End times for each item in your list.

This approach allows you to automate the calculation of time differences directly within your SharePoint list.