当您在Microsoft Excel(Microsoft Excel)中处理时间表之类的内容时，您可能需要获取时间之间的差异。例如，您可以输入需要查看工作小时数的开始时间和结束时间。
我们将向您展示几种在Excel 中查找项目跟踪、工作时间表或员工日程表中两个时间之间差异的方法。
开始之前，格式化时间
要正确使用此处描述的函数和公式，请务必对单元格中的时间值使用 Excel 的时间格式之一。
在“主页”选项卡上的(Home)“数字”(Number)下拉框中选择“时间”(Time)，或使用如下所示的“设置单元格格式”(Format Cells)对话框。
使用时间函数(Time Functions)获得基本结果(Basic Results)
获取两个时间之间的小时、分钟或秒的最简单方法是使用Excel(Excel)中的相同命名函数：HOUR、MINUTE和SECOND。
每个的语法与函数名称和括号中减去的单元格相同。让我们看一下每个例子。
要获取单元格 B2 和 B1 中时间之间的总小时数，您可以使用以下公式：
=小时(B2-B1)
仅对于单元格 B2 和 B1 中的时间之间的分钟数，您可以使用以下公式：
=分钟(B2-B1)
如果您需要单元格 B2 和 B1 中的秒数差异，请使用以下公式：
=第二(B2-B1)
虽然这些函数非常适合提供总小时数、分钟数或秒数，但它们仅限于时间的这些特定部分。如果您需要更多信息，例如小时和分钟，可以使用下一个选项。
使用减法(Use Subtraction)并手动设置(Manually Format)结果格式(Result)
就像在 Excel 中(values in Excel)使用减号减去日期(subtracting dates)或数值一样，您也可以对时间执行相同的操作。不同的是，计算差异后，您必须手动将结果格式化为小时、分钟和秒。让我们来看看。
这里我们想要单元格 B2 和 B1 之间的总差异，包括小时和分钟。您将输入以下Excel公式：
=B2-B1
您会注意到结果的格式是时间而不是小时数和分钟数。这是一个快速的变化。
- 选择包含结果的单元格，然后执行以下操作之一以打开格式设置选项：
- 右键单击(Right-click)，选择“设置单元格格式”(Format Cells)，然后转到“数字”(Number)选项卡。
- 选择“主页”选项卡上的(Home)“数字”(Number)下拉框，然后选择“更多数字格式”(Number Formats)。
- 在“数字”(Number)选项卡上，选择左侧的
“自定义格式”。(Custom)
- 在右侧，使用滚动框选择格式“h:mm”，然后选择“确定”。
然后您应该会看到以小时和分钟为单位的时差。
使用上面相同的公式减去时间并使用不同的格式，您可以显示小时、分钟和秒，或者仅显示分钟和秒。在“设置单元格格式”(Format Cells)对话框中分别选择“h:mm:ss”或“mm:ss”。
此选项很简单，因为您只需减去时间值即可；但是，您确实需要手动格式化结果的额外步骤。
使用TEXT(TEXT Function)函数(Result)自动格式化(Automatically Format)结果
在Excel(Excel)中计算时间差的另一种方法是使用TEXT函数。您仍将减去包含时间的单元格，但将结果显示为特定格式的文本。
语法为TEXT（值，格式），格式参数放在引号中。以下是使用此选项的几个示例。
要减去单元格 B2 和B1(B1and)中的时间并将结果格式设置为小时，您可以使用以下公式：
=TEXT(B2-B1,”h”)
要减去相同单元格中的时间并使用小时和分钟格式化结果，请使用以下公式：
=TEXT(B2-B1,“高:毫米”)
如果您想包含秒数，只需在格式参数中的分钟后面添加此内容，如下所示：
=TEXT(B2-B1,”时:分:秒”)
使用TEXT函数，您不仅可以获得时间之间的差异，还可以同时正确格式化它。此选项的唯一缺点是结果单元格的格式为文本(cell is formatted as text)，使其更难以在其他计算中使用。
慢慢来”
在Microsoft Excel(Microsoft Excel)中执行时间计算并不像您想象的那么简单。但是，使用这三种方法，您可以通过简单的公式减去时间来获得工作时间、休息时间或类似时间。花点“时间”看看哪一个在您的工作表中最有效。
有关更多 Excel 教程，请查看如何修复(how to fix formulas)无法正常工作的公式。
How to Calculate the Difference Between Two Times in Microsoft Excel
When you work on something like a timesheet in Microsoft Excel, you may need to get the difference between times. Fоr instanсe, you may enter start time and end time where you neеd to see the numbеr of hours worked.
We’ll show you a few ways to find the difference between two times in Excel for project tracking, a work timesheet, or an employee schedule.
Before You Begin, Format the Times
To correctly use the functions and formulas described here, be sure to use one of Excel’s time formats for the time values in your cells.
Either select Time in the Number drop-down box on the Home tab or use the Format Cells dialog box shown below.
Use the Time Functions for Basic Results
The simplest way to get the hours, minutes, or seconds between two times is using the same named functions in Excel: HOUR, MINUTE, and SECOND.
The syntax for each is the same with the function name and subtracted cells in parentheses. Let’s look at an example of each.
To get the total hours between the times in cells B2 and B1, you’d use this formula:
=HOUR(B2-B1)
For only the number of minutes between the times in cells B2 and B1, you’d use this formula:
=MINUTE(B2-B1)
If you want the difference in seconds in cells B2 and B1, use the following formula:
=SECOND(B2-B1)
While these functions are ideal for providing the total number of hours, minutes, or seconds, they are limited to those specific parts of the time. If you need more, such as both the hours and minutes, you can use the next option.
Use Subtraction and Manually Format the Result
Just like subtracting dates or numeric values in Excel using the minus sign, you can do the same with times. What’s different is that after you calculate the difference, you must manually format the result as hours, minutes, and seconds. Let’s take a look.
Here we want the total difference between cells B2 and B1 including hours and minutes. You would enter the following Excel formula:
=B2-B1
You’ll notice that the result is formatted as a time rather than the numbers of hours and minutes. This is a quick change.
- Select the cell with the result and do one of the following to open the formatting options:
- Right-click, pick Format Cells, and go to the Number tab.
- Select the Number drop-down box on the Home tab and choose More Number Formats.
- On the Number tab, choose the Custom format on the left.
- To the right, use the scroll box to pick the format “h:mm” and select OK.
You should then see your time difference in hours and minutes.
Using the same formula above to subtract the times and use different formatting, you can display the hours, minutes, and seconds or just the minutes and seconds. In the Format Cells dialog box pick “h:mm:ss” or “mm:ss” respectively.
This option is simple because you’re merely subtracting the time values; however, you do have the extra step of manually formatting the result.
Use the TEXT Function to Automatically Format the Result
One more way to calculate a difference in times in Excel is using the TEXT function. You’ll still subtract the cells containing the times but display the result as text in a specific format.
The syntax is TEXT(value, format) with the format argument placed in quotes. Here are a few examples using this option.
To subtract the times in cells B2 and B1and format the result as hours, you’d use this formula:
=TEXT(B2-B1,”h”)
To subtract the times in the same cells and format the results with hours and minutes, use this formula:
=TEXT(B2-B1,”h:mm”)
If you want to include seconds, simply add this after the minutes in the format argument as shown here:
=TEXT(B2-B1,”h:mm:ss”)
With the TEXT function you can not only obtain the difference between your times, but format it correctly at the same time. The only downside to this option is that the result cell is formatted as text making it more difficult to use in another calculation.
Take Your “Time”
Performing time calculations in Microsoft Excel isn’t as straightforward as you might think. However, using these three methods, you can subtract time to obtain working hours, break minutes, or similar times with a simple formula. Take the “time” to see which works best in your worksheet.
For more Excel tutorials, look at how to fix formulas that aren’t working correctly.
