How to Calculate Time in Excel With Formulas

Understanding Time Values in Excel

Excel stores time as decimal numbers between 0 and 1, where each whole number represents one full day. This system allows Excel to perform mathematical operations on time values just like it does with regular numbers. Understanding this foundation is essential for working with time calculations.

Learn About Energy Bill Assistance Programs →

In Excel's time system, the number 0 represents midnight (12:00 AM), 0.5 represents noon (12:00 PM), and 1 represents the next midnight. Each hour equals approximately 0.041667 of a full day (calculated as 1 divided by 24 hours). Each minute equals approximately 0.000694 of a full day (calculated as 1 divided by 1,440 minutes in a day). Each second equals approximately 0.0000116 of a full day.

When you enter a time like 3:30 PM into an Excel cell, the program converts it to its decimal equivalent (0.645833 for 3:30 PM). This decimal representation enables you to add, subtract, and manipulate times using mathematical formulas. If you format a cell containing 0.645833 as time, it displays as 3:30 PM. If you format the same cell as a number, it shows the decimal value.

This decimal system also explains why you can subtract one time from another. For example, subtracting 9:00 AM from 5:00 PM gives you 8 hours because Excel is simply subtracting the decimal values (0.708333 minus 0.375 equals 0.333333, which equals 8 hours when multiplied by 24).

Practical takeaway: Format your time result cells appropriately. If you subtract two times and get a decimal result like 0.333333, format that cell as [h]:mm or [h]:mm:ss to display it as 8:00 or 8:00:00 instead of a decimal number.

Basic Time Subtraction: Calculating Hours and Minutes Between Times

Time subtraction is one of the most common calculations in Excel, whether you're tracking work hours, measuring elapsed time, or calculating duration between events. The basic formula is straightforward: subtract the start time from the end time.

Get Your Free Car Buying Guide →

If your start time is in cell A1 (9:00 AM) and your end time is in cell B1 (5:30 PM), enter this formula in cell C1:

• =B1-A1

The result will be 0.3125 in decimal format, which represents 7.5 hours. To display this as time format (7:30), select cell C1, right-click, choose "Format Cells," select the "Time" category, and choose a format like [h]:mm. The brackets around the h are crucial—they allow the display of hours greater than 23, which is necessary when calculating time spans longer than one day.

For times that span midnight (like 11:00 PM to 3:00 AM), you need to account for the day change. If the end time appears earlier than the start time, add 1 to your formula:

• =IF(B1<A1,B1+1-A1,B1-A1)

This formula checks whether the end time is less than the start time. If it is, it adds 1 (representing one full day) to the end time before subtracting. If the end time is already greater than the start time, it performs a simple subtraction.

Common formatting options for time results include:

• [h]:mm displays total hours with minutes (example: 7:30)
• [h]:mm:ss displays total hours, minutes, and seconds (example: 7:30:45)
• h:mm displays hours under 24 with minutes (example: 7:30, but will show 7:30 even if the actual duration is 31 hours)

Practical takeaway: Always use the [h]:mm format with brackets for elapsed time spanning multiple hours, especially when calculating work hours or durations that might exceed 24 hours. The brackets ensure accurate display of longer time periods.

Converting Time to Decimal Hours for Calculations

Many situations require converting time differences into decimal format for payroll calculations, billing purposes, or data analysis. Converting elapsed time to decimal hours makes these calculations straightforward in spreadsheets.

Get Your Free Atlanta Airport Sleeping Guide →

If you have calculated elapsed time in cell C1 using the formula =B1-A1, and it's currently formatted as time, you can convert it to decimal hours by multiplying the result by 24 (since 1 full day equals 24 hours):

• =(B1-A1)*24

For example, if an employee works from 9:00 AM to 5:00 PM, the calculation would be (5:00 PM minus 9:00 AM) times 24, which equals 8 hours. If the work period is 9:00 AM to 5:30 PM, the result would be 8.5 hours.

To convert to decimal minutes, multiply by 1,440 (the number of minutes in a day):

• =(B1-A1)*1440

To convert to decimal seconds, multiply by 86,400 (the number of seconds in a day):

• =(B1-A1)*86400

For payroll applications where you need decimal hours to two decimal places (like 8.50 instead of 8.5), use the ROUND function:

• =ROUND((B1-A1)*24,2)

The "2" in the ROUND function specifies two decimal places. You can adjust this number based on your needs—use 1 for one decimal place, 3 for three decimal places, and so on.

Many businesses use decimal hours for billing clients by the hour. If an employee bills $50 per hour and worked 6.75 hours, the billing calculation would be 6.75 multiplied by $50, equaling $337.50. This decimal format makes such calculations much simpler than working with hours and minutes separately.

Practical takeaway: Create a separate column for decimal hours when working with time data. Use the formula =(B1-A1)*24 and format as a number with two decimal places for clean payroll or billing calculations.

Using the TIME Function to Create Specific Times

The TIME function allows you to construct specific time values by specifying hours, minutes, and seconds as separate components. This function is useful when you need to create time values from component parts or perform calculations based on specific time requirements.

Learn How to File an Amended Tax Return →

The TIME function syntax is:

• =TIME(hours, minutes, seconds)

Each parameter accepts numbers from 0 to 59 for minutes and seconds, and 0 to 23 for hours in standard 24-hour format. For example:

• =TIME(14,30,0) creates 2:30 PM
• =TIME(9,0,0) creates 9:00 AM
• =TIME(23,59,59) creates 11:59:59 PM

The TIME function becomes particularly powerful when combined with other functions. For instance, if you want to calculate how much time remains until the end of a workday (assuming 5:00 PM end time), you could use:

• =TIME(17,0,0)-A1

where A1 contains the current time. If A1 contains 3:00 PM, this formula returns 2 hours (displayed as 2:00 if formatted as time, or 2 if formatted as a number).