ExcelInsider Team
Working with dates and times in Excel is a common task for anyone managing schedules, tracking work hours, or analyzing project durations. Excel allows you to calculate differences accurately between dates, times, or a combination of both. Understanding these methods can save hours of manual calculation and reduce errors, whether you’re handling simple daily logs or complex project timelines.
In this article, we will explore six effective methods to subtract date and time in Excel. Each method includes clear steps and practical examples using a sample dataset, so you can apply them immediately to your own spreadsheets.
Steps to subtract date and time in Excel:
➤ Select the cell where you want to display the elapsed time.
➤ In cell D2, type the following formula:
=INT(C2-B2)&” days “&TEXT(C2-B2,”h”” hrs “”m”” mins “””)
➤ Replace B2 with the start date cell and C2 with the end date cell.
➤ Press Enter to calculate.
➤ Drag the formula down to apply it to all rows/projects.
Formatting Elapsed Time as Days, Hours, and Minutes with INT and TEXT Functions
When you want to show the difference between two dates in a clear, human-readable way, using the INT and TEXT functions is ideal. This method outputs the total days along with hours and minutes in a single, easy-to-read string. It’s perfect for dashboards, reports, or any sheet where clarity matters.
We’ll use the following dataset:
Steps:
➤ Select the cell where you want to display the elapsed time.
➤ In cell D2, type the following formula:
=INT(C2-B2)&" days "&TEXT(C2-B2,"h"" hrs ""m"" mins """)
➤ Replace B2 with the start date cell and C2 with the end date cell.
➤ Press Enter to calculate.
➤ Drag the formula down to apply it to all rows/projects.
Calculating Exact Hours and Minutes Using HOUR and MINUTE Functions
If you need the difference between two times in precise hours and minutes without showing days, the HOUR and MINUTE functions work best. This approach is simple, clean, and perfect for timesheets or schedules where only time intervals matter.
Steps:
➤ Select the cell where you want the result.
➤ Open the Format Cells tab using Ctrl + 1 . Select Custom >> Type [h]:mm to display hours and minutes correctly and click OK.
➤ In cell D2, type the following formula:
=TIME(HOUR(C2)-HOUR(B2), MINUTE(C2)-MINUTE(B2), 0)
Replace B2 with the start time cell and C2 with the end time cell.
➤ Press Enter to calculate the difference.
➤ Drag the formula down to apply it to all rows/projects.
Displaying Hours and Minutes Using the TEXT Function
When you want a neat, readable output showing the difference in hours and minutes, the TEXT function is ideal. It formats the elapsed time cleanly, making it easy to read in reports, schedules, or dashboards.
Steps:
➤ Select the cell where you want to display the result.
➤ In cell D2, type the following formula:
=TEXT(C2-B2,"h"" Hours ""m"" Mins""")
➤ Replace B2 with the start time cell and C2 with the end time cell.
➤ Press Enter to calculate.
➤ Drag the formula down to apply it to all rows/projects.
Computing Precise Durations in Hours, Minutes, and Seconds Using TEXT Function
When you need exact elapsed time including hours, minutes, and seconds, the TEXT function is ideal. This method produces a clear, readable output in the familiar hh:mm:ss format, perfect for timesheets, logs, or detailed schedules.
Steps:
➤ Select the cell where you want to display the result.
➤ In cell D2, type the following formula:
=TEXT(C2-B2,"hh:mm:ss")
➤ Replace B2 with the start date-time cell and C2 with the end date-time cell.
➤ Press Enter to calculate.
➤ Drag the formula down to apply it to all rows/projects.
Calculating Elapsed Time Up to the Current Moment Using NOW or TODAY Function
If you want to track how much time has passed up to the present, the NOW or TODAY function is perfect. This method is ideal for ongoing projects or tasks where the end date isn’t fixed, giving real-time updates. We’ll use the following dataset for demonstration:
Steps:
➤ Select the cell where you want to display the elapsed time.
➤ In cell C2, type the following formula:
=TEXT(NOW()-B2,"d"" days ""h"" hours ""m"" mins ""s"" secs""")
➤ Replace B2 with your start date/time cell.
➤ Press Enter to calculate.
➤ The result will automatically update as time progresses.
Subtracting a Specific Amount of Time Using the TIME Function
If you need to deduct a set number of hours or minutes from a given time, the TIME function makes it simple and precise. This approach is useful for adjusting schedules, accounting for breaks, or recalculating deadlines. We’ll use the following dataset for demonstration:
Steps:
➤ Select the cell where you want the result.
➤ Open the Format Cells tab using Ctrl + 1 . Select Custom >> Type [h]:mm to display hours and minutes correctly and click OK.
➤ In cell D2, type the following formula:
=B2-TIME(0,C2,0)
➤ Press Enter to calculate.
➤ Drag the formula down to apply it to all rows/projects.
Frequently Asked Questions
How do I subtract only hours between two times in Excel?
You can use the HOUR and MINUTE functions together to calculate the exact difference between two times in Excel. Format the result in hours and minutes, which ensures you do not include full days unnecessarily.
Can Excel calculate negative time differences?
Yes, Excel can display negative time differences if you format the cell as [h]:mm:ss or use the TEXT function. This allows proper calculation even when the end time is earlier than the start time without showing errors.
What if my start and end dates are in different formats?
Ensure both start and end date-time cells are formatted consistently as Date & Time in Excel. Using inconsistent formats can lead to incorrect calculations or errors when subtracting dates, so proper formatting is critical for accurate results.
Is it possible to subtract dates automatically as new entries are added?
Yes, by using formulas like NOW or referencing dynamic cells, Excel can automatically recalculate the difference whenever new start or end dates are entered, keeping your spreadsheet up-to-date and eliminating the need for manual updates for every entry.
Wrapping Up
In this tutorial, you learned six different ways to subtract dates and times in Excel. Each method is suited for specific needs, from displaying total days to precise hours, minutes, and seconds. By applying these techniques, you can handle project timelines, time tracking, and dynamic calculations efficiently. Feel free to download the practice file and share your feedback.
