How to Calculate Time in Excel?

Calculating time in Excel is straightforward and involves entering start and end times, then using a simple formula to find the difference.

Steps to Calculate Time:

1. Enter Start and End Times: In two separate cells, enter the start and end times. For example, in cell A2, enter "8:00 AM" and in cell B2, enter "5:00 PM". Excel recognizes these as time values.

2. Calculate the Difference: In a third cell (e.g., C2), enter the formula =B2-A2 and press Enter. This subtracts the start time from the end time, giving you the elapsed time.

3. Format the Result: The result might initially appear as a decimal number. To display it as a time value, right-click on the cell (C2) and select "Format Cells."

4. Choose Time Format: In the "Format Cells" dialog box:

   • Select the "Number" tab.
   • Choose "Time" from the category list.
   • Select a suitable time format. For example, h:mm displays hours and minutes (e.g., 9:00), or [h]:mm displays total elapsed hours exceeding 24 hours. h:mm AM/PM shows hours, minutes and AM or PM.
   • Click "OK."

Example

Calculating Time Differences that Span Multiple Days

If the time period spans multiple days, you'll need to include the date in both the start and end times. For example:

• A2 (Start Date & Time): 8/30/2024 8:00 AM
• B2 (End Date & Time): 8/31/2024 5:00 PM

The formula =B2-A2 will still work, but you might need to format the result using a custom format like d h:mm to display the number of days, hours, and minutes. The format [h]:mm will calculate the total elapsed hours.

Calculating Total Hours Worked

You can easily calculate total hours worked by summing time differences. For example, if you have several rows of start and end times (A2:B2, A3:B3, etc.), you can use the SUM function:

=SUM(B2:B10-A2:A10)

Remember to format the cell containing the sum using a custom format like [h]:mm to accurately display the total elapsed hours. Without the square brackets, Excel will reset to 0 after reaching 24 hours.

Additional Tips

• Entering Times: You can enter times directly in Excel using a 12-hour (e.g., "8:00 AM") or 24-hour format (e.g., "08:00").
• Decimal Values: Excel stores times as fractions of a day. For example, 6:00 AM is 0.25 (24 hours / 4).
• Negative Time: If the start time is after the end time, the result will be negative. Excel can have issues displaying negative times.
• Considerations: Always verify that your Excel settings (date and time formats) are set correctly.

By following these steps, you can easily and accurately calculate time in Excel for various applications.